[sims] transaction logs

[zag]

Should I be doing anything with the transaction logs for Sims?

 

I've just setup our backup and its complaining that the transaction logs are not being backed up and could grow exponentially.

 

V-79-40960-37914 - Database Sims is configured to maintain transaction logs.  Transaction log backups are not being performed.  This will result in the log growing to fill all available disk space.  Regular log backups should be scheduled or the database should be changed to the simple recovery mode. 

 

 

EDIT: I just noticed our LDF file is 5.6gb

Edited September 17, 2010 by zag

[vikpaw]

Have you just moved server or to the new SQL 2008?

 

The regular backups should truncate the transaction logs. 5.6 is immense and will defo cause you problems.

 

I'm no SQL expert but there are ways to just run Truncate commands.

 

I would try a system manager backup from SIMS itself, this used to do the job, if that doesn't work, try a Full Backup using SQL management studio which should also truncate the log file. If they don't work, post back.

 

I'm not sure now, but the old routine used to create a backup and then zip it too, but to do all that requires as much free space as you are backing up, so depending on the size of your MDF i'd ensure that you have at least 5.6GB plus mdf size free.

 

A very long time ago, at another school, in a country far far away, we had massive truncation problems and one reason for the massive growth was that it was set to expand by 10%, this was easily curbed by setting it to increase by a fixed amount, e.g. 100MB, so it wouldn't spiral out of control, but it does need close monitoring on a regular basis, or set up alerts for a long term solution.

 

Hope you get it sorted quickly.

Edited September 18, 2010 by vikpaw
edit

[Cache]

If you download the ESS Upgrade utility from Capita Education Support Services (Link is under the latest patch) and run it, log in to the Database as if you were going to do an upgrade and then click on Tools.

 

It should then show you the log file sizes and give you the option to truncate them if I remember rightly. Can't check right now, but made a point of asking the engineer who did our Migration how to manage it.

[zag]

Thanks, I worked out its just because we are still using SQL 2005.

 

I guess when I finally bother doing the upgrade to SQL 2008 this problem will disappear.

[vikpaw]

@jinnantonnix - that's confused me know because in the past i've had people refer to truncating of the logs as also reducing the physical file size. in fact the sysman backup used to do just that and reduce the .ldf file to 1024kb from however many MB it had reached. I guess this was just bad use of technical SQL terminology.

So really what is needed is to shrink the log file, and part of doing that could involve truncating it too...? sounding closer to reality?

[zag]

Backupexec 2010