[sims] LDF growing very quickly

[rhyds]

My SIMS LDF file has reached 300Mb+ over the last few days, and looks like continuing to grow. Trying to shrink it using the SQL server management studio doesn't seem to be working. Is there another way to reduce its size? Apparently there is a capita tool to fix this issue, but I can't find it.

[spc-rocket]

Hi there,

 

Are you using Bromcom system with sims?

 

Ash.

[Bromcom_John]

The advisable way to reduce the Log file size is to execute a SIMS Sytem manager backup.

This should truncate the log file along with the backup process.

[rhyds]

Yes we are using bromcom.

 

This has happened before (just after going to SQL 2005), the thing is that time I dettached the DB, deleted the LDF and re-attached it with a new LDF. Our SIMS LEA support officer told me that there was a capita tool that was safer than doing this (no ammount of backups would truncate it, and it had reached 11Gb).

[Bromcom_John]

@Rhyds - It occurs to some degree or another when any third party is involved with communicating to the SIMS System. We can make efforts to reduce the communication frequency with which our software communicates with SIMS, but I will of course need to know which school you are at If you wish to retain your user anonymity then please drop me a PM.

This can assist in reducing the rapidity with which the log files fills but ultimately you will still need to regularly enact a SIMS System Manager backup to reduce the Log file size.

[rhyds]

We run a nightly scheduled backup of SIMS using the internal SQL 2005 backup routines, so that's not a problem.

 

John: PM on the way

[vikpaw]

we had oodles of problems with this and had LDFs reaching over 20GB, yes gig not meg!

 

we were always told that bromcom was to blame but got growth even with bromcom off the network. for example, in controlled conditions just the act of opening the attendance module would increase the size of the LDF.

 

a system manager backup will truncate the logs and supposedly can even be run when users are on the system, but NOT however users of FMS. Anything that can be truncated will be. I always feel safer if everyone is out of the system though.

 

the backup routine provided by our local support team wouldn't truncate the log no matter what we did. eventually post migration to 2005 the routine was changed to include a dbcc shrinkfile command to force truncation.

 

other things to bear in mind are how the log is set to grow, i think by default ours was on a fixed percentage, which is fine when truncation is occurring but when it fails this grows exponentially. we found it better to set it to grow by a fixed size e.g. 100Mb, that way you can see what is going on, and it slows the growth rate down. Admittedly ours was an extreme example.

 

Talk to Bromcom and make sure that the over night processes and imports don't coincide with the sims backups. we carefully altered our times so that they don't clash. if there is an overlap with times, things can really escalate, but the guys at bromcom can help you out with it.

 

bromcom also have a very informative helpsheet covering SQL and transaction logs though i didn't end up using it as we're not supposed to run sql commands direct on our server. i've attached it in case it's of use to you.

 

i just used management studio to alter the way the log grows to a fixed size, since then with the alteration to the backup routine by local support, we've not had a problem thankfully. every morning the log file is at a static 100MB so some process causes that little bit of growth up from the 1024k minimum. In the past few months its never gone above that.

 

give us a shout if you want more help with bromcom side of things (we need to stick together), i used to have an issue when we forced high grade photos onto sims as the bromcom import was crashing and taking longer to complete which had knock on effects. But that's a whole different story!

Dealing with a large transaction log file.doc