[SIMS] massive sim.ldf file - anyone else had there's suddenly grow?

[SHimmer45]

logged a call with our SIMS team as the log file has reached almost 60GB which is plain daft.
The SIMS team setup the server ages ago but they havent installed any of the SQL management suite to actually look and what logging options have been set?

Not that happy with installing the suite just in case they grumble about it being unsupported.
I know they have recently pulled the autumn upgrade and wondering if this might be a reason for this.

[jinnantonnixx]

It's quite likely that your backups are not truncating the log file.

What's your backup mechanism?

[creese]

Have you tried the ShrinkLDF utility?

[LukeC]

It's quite likely that your backups are not truncating the log file.

I second this, we have had this before there is a setting that can be change but can not remember it.

[jinnantonnixx]

Before attempting to shrink the file (in itself it's not going to cause too much of a problem, apart from being very big) it's best to try and find out why it's been growing, or we'll end up with the same problem again.

We need to know the database model (simple/full) and the backup mechanism used.

[theriver]

You can install the SQL Management tools on another box and connect to the database server to figure out what's going on (assuming the DB server accepts remote connections).

[SHimmer45]

after speaking with the SIMS team they ran a script to change the logging/backup method to simple as it was set to full.
further investigation suggests that the backup also had a problem and wasnt completing properly and not truncating the logs..

[jinnantonnixx]

Good job you spotted the problem.

[Quatermass]

after speaking with the SIMS team they ran a script to change the logging/backup method to simple as it was set to full.
further investigation suggests that the backup also had a problem and wasnt completing properly and not truncating the logs..

We've suffered from the "recovery model Simple/Full issue" bloating LDFs in the past. Having set it to Simple manually a few times (SQL Server Management Studio > Database Properties > Options > Recovery Model) I tend to check it after each SIMS release because several have kindly set it back to Full again!

[zag]

So how big is the database now?

You can also change the autogrowth properties to slow it down. We have it set to 10mb auto growth here.

Sims mdf is 2gb
the ldf db is 10gb

Is there any document saying what a good ratio should be?

[localzuk]

So how big is the database now?

You can also change the autogrowth properties to slow it down. We have it set to 10mb auto growth here.

Sims db is 2gb, the log db is 10gb

10gb is huge still... Ours ldf is 1.1Mb at the moment, and it'll grow over the day to probably 10Mb or so, then the backup will truncate it back down to about 1Mb again.

[SHimmer45]

currently the LDF stands @ 1.5MB.
the SIMS team have a script to set the properties if SQL studio isnt installed.

[jinnantonnixx]

Avoid using auto-growth as a management tool.


Always size the databases correctly, an auto-grow will halt the database engine while the server extends the files.

Switch on auto-grow by all means, but set it so that the increment is a large size (rather than a percentage). This means that if the server ever needs it (for whatever reason), it will extend the files by a large, defined increment, rather than nudging the file size every so often.


Forget auto-shrink, do this manually if you must.

Here's why.

http://blogs.msdn.com/b/sqlserversto...hrink-off.aspx

[zag]

I just reduced to 10mb by using Shrink >> file >> log

99% was free apparently!

Set a limit of 1gb now.

[zag]

Anyone using backupexec 2010? What settings for the SQL agent are you using to truncate the logs?