[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?

Edited December 9, 2011 by zag

[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/sqlserverstorageengine/archive/2007/03/28/turn-auto-shrink-off.aspx

Edited December 9, 2011 by jinnantonnixx

[zag]

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

 

99% was free apparently!

 

Set a limit of 1gb now.

Edited December 9, 2011 by zag

[zag]

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

[vikpaw]

I may be missing something here, but i thought that SIMS no longer uses the ldf and causes an increase, or is this still tied to the model.

Mine is currently at 130MB and i don't think it increases much at all. It just cleans itself up and reuses the space. When i moved to my new server i fiddled around a lot, and probably had a lot of long conversations with the SQL Guru ---> @jinnantonnix

 

Here's some stuff from my notes.

 

@zag - A percentage increase is dangerous like @jinnantonnixx - better to make it a fixed size, the docs i've read, suggest an eighth of something, but i can't remember exactly how it was worked out. I remember working it out based on my 4GB mdf. I think it suggested that the ldf could be up to a quarter of the MDF but dont quote me on that, and the growth would be an eighth. So the DB is set to grow at 100MB. unrestricted and the LDF by 125MB unrestricted.

 

If you use management studio, you can right click on the db and use the shrink command but best to do it out of hours and after a backup to be safe. If you choose shrink files, the next page in the wizard will allow you to view each file, it's size and the free space. I currently have a 4.5GB mdf with 20% free and the 130MB LDF with 80% free.

 

Despite the free space, in order for the ldf to be truncated you need to have done a transaction log file backup first. Otherwise it just reports successfully but doesn't always reduce the size.

 

We do the backups in Management studio only, and it's a scheduled nightly task. I don't routinely backup the logs. The files are then backed up by the Backup exec routine.

 

We used to have a sql agent going in, but you have to be careful that it is your only backup system, we had multiple, and then what happens is, if your backup system is doing a grandfather strategy, with daily differentials / incrementals as ours was, any other backup especially a full one will break that differential routine and you can't recover from it. It's not so bad, if it just goes in and does a full backup, but you need to set it up specifically.

 

I would like to back up more than once a day, or take the logs out, but find it's probably overkill, as mostly we couldn't afford to go back in time and lose any work, so all we'd do is restore an old backup and recover what work we could on a test system. Then bring back into live. Log shipping and fancy stuff, like playing back transactions just seems too complex for something we might not need to do. Save that job for a summer when there is nothing else planned!

 

I do frequently restore the backups to a test server though.

 

sorry, digressing a bit. I don't think the LDF should be growing, certainly not to above the size of the MDF. Do you people have third party software that is going in and accessing SIMS, or even a home developed system that is running a report against the server direct.

[jinnantonnixx]

Hi Vik - guru? (blush), no I don't think so.

 

The log file is always used by SQL server, no matter what database model you use. It's nothing to do with SIMS or any other app, it's the way the SQL server operates.

Any change made by SIMS, for instance, is first written to the log file by the server. This is so that in the event of the cleaner pulling the socket out of the wall, when the SQL server starts up it will be able to check for uncommitted transaction stored in the log file. It scans through these and commits any un-committed logs into the database.)

 

So, no matter what model of database you use, the log file is an integral part of the database system.

 

There's some confusion over how the log file is 'used'. It's always used by SQL server, it needs it to maintain a consistent state. But in the case of backups, not so.

In the case of a 'full' backup model, you can restore the database to a point in time by restoring the database then restoring all or some of the transactions in the log file, to any point in time. This is the most flexible system, but requires planning.

In the case of the 'simple' database model, the log file is not (and cannot) to used as part of the restore - it is only used by the SQL server to maintain consistency and to recover its state in the event of a crash or power failure. The simple model only permits you restore a whole backup, any changes made after the last backup will be lost.

 

 

Next, why does the log file get bigger? When the SQL server 'knows' that the database has been backed up, it can be sure that the items in the log file before that backup are not needed. After all, we have a backup of that database at that point. The log file is truncated, which means that the portion of disk previously used by the old transaction items can be re-used. A bit like a tape-loop. All being well, and backups running regularly, the log file doesn't grow; it is reused.

If backups go wrong, the SQL server is unable to truncate the log file is it cannot guarantee that the data can be recovered - we haven't backed up the database, so it must keep the logs in case we need to commit the logs into a restored database to bring it up to date. Of course, the 'tape loop' concept fails as we run out of space, so it must extend the physical size of the file, hence the increase in log size.

 

Be aware that truncating the log is not the same as shrinking the log. Truncating the log makes virtual markers in the log indicating that sections of the log file can be re-used. This doesn't resize the file. Shrinking the file actually reduces the physical space the file takes up by re-organising the log file and throwing away unused space. Unless something crazy has happened (as in @SHimmer45 's case) there's no need to bother shrinking the log file.

Edited December 10, 2011 by jinnantonnixx

[vikpaw]

Hi Vik - guru? (blush), no I don't think so.

...

I think your post proves otherwise! Thanks for correcting me again, i'm always interchanging shrink and truncate when i shouldn't be.

I think my idea about SIMS no longer using it was from when it all moved to SQL 2008 as standard, as it handles log files differently right? I'm not sure exactly how, and i think they now recommend using Simple Mode. Trying to find the notes that mentioned it, but coming up with nothing at the moment.

[Edu-IT]

Our LDF file is around 40MB. Should I be concerned? The FMS LDF is about 180MB.

[vikpaw]

Our LDF file is around 40MB. Should I be concerned? The FMS LDF is about 180MB.

No 40 is fine, its more about how much it's growing. 180 for FMS seems big in comparison but it depends on how big the mdf is and what it's initial size is. So long as you're backing up regularly and keep an eye on it it should be fine. In management studio right click on the db, tasks, shrink, files and see how much is free, it's probably mostly empty. right click on db and properties will tell you what the initial size it, that might be the default.

[Rawns]

We've had endless issues with this since the SQL2008 migration. The migration tools did not put the database's into simple mode after they were migrated not to mention some SIMS upgrades changing the recovery mode. Also the Capita Datafix patches do not work logically. It seem to put the database into simple mode, truncate the log file down to it's base size and then for some crazy reason only known to Capita, put's the databases back into full recovery mode meaning the log file will happily grow and grow again! I have a call logged with them to address this and the reworked patch is waiting the data managers approval. As this has been with him for a month or two now, I got fed up waiting so have put together a batch file to put the DB into simple mode and truncate it:

 

@echo off
PushD %~dp0

:: Put the SIMS database into SIMPLE recovery mode and truncate the log file
ECHO ============================================================================
ECHO Changing recovery mode for SIMS:
sqlcmd -S%COMPUTERNAME%\SIMS2008 -Usa -P[password] -Q"ALTER DATABASE SIMS SET RECOVERY SIMPLE"
sqlcmd -S%COMPUTERNAME%\SIMS2008 -Usa -P[password] -Q"USE [sIMS] DBCC SHRINKFILE (N'sims_log' , 0, TRUNCATEONLY) WITH NO_INFOMSGS"
ECHO Done.
ECHO ============================================================================
ECHO. 

:: Put the FMS database into SIMPLE recovery mode and truncate the log file
ECHO ============================================================================
ECHO Changing recovery mode for CCSFMS:
sqlcmd -S%COMPUTERNAME%\SIMS2008 -Usa -P[password] -Q"ALTER DATABASE %FMSDB1% SET RECOVERY SIMPLE"
sqlcmd -S%COMPUTERNAME%\SIMS2008 -Usa -P[password] -Q"USE [CCSFMS] DBCC SHRINKFILE (N'ccsfms_log' , 0, TRUNCATEONLY) WITH NO_INFOMSGS"
ECHO Done.
ECHO ============================================================================
ECHO.

:List the databases specified and output their recovery modes
ECHO ============================================================================
sqlcmd -S%COMPUTERNAME%\SIMS2008 -Usa -P[password] -Q"SELECT name AS [DB], recovery_model_desc AS [RM] FROM sys.databases WHERE (name='SIMS') OR (name='CCSFMS')"
ECHO ============================================================================
EXIT 

 

Feel free to use it if required, you can always copy the blocks of code and add any additional databases that may exist. You don't need management studio installed to run it either. You can always quickly run it after an upgrade/patch has been applied to ensure the databases are in simple mode as I'm sure a couple of patches have applied to different SIMS systems put the DB back into full recovery mode.

Edited December 12, 2011 by Rawns

[vikpaw]

I noticed that! One of their solutions was to switch recovery model, didn't realise it went back again!

[Rawns]

I noticed that! One of their solutions was to switch recovery model, didn't realise it went back again!

 

I'm not sure the patch was always ran in that way either. I think it must have been changed a while back to put the databases back to full recovery mode, no idea when though. It's not ideal with schools that struggle with free space. the large log files also seem to affect performance too. We had one school report major slowdown and their SIMS LDF file had hit over 60Gb! Ran the script above and it shrunk down to 2Mb and performance was suddenly back to normal! We've also had odd reports of FMS instability and again, it's been down to the LDF file growing to a crazy size.

 

As we're using CentraStage at our LA, once an upgrade has been applied, we can run this script against every SIMS server and within 10 minuets, we can be sure all the SIMS and FMS databases are still in simple mode.

[jinnantonnixx]

If you're curious to see how much of your SQL files are actually in use, as well as the physical size on disk, you can run this command in a query window.

 

 

use [the name of your database]
go

SELECT	Name AS [Logical Filename], Filename AS [Physical Filename],

CONVERT(Decimal(15,2),ROUND(a.Size/128.000,2)) [size on Disk (MB)],

CONVERT(Decimal(15,2),ROUND(FILEPROPERTY(a.Name,'SpaceUsed')/128.000,2)) AS [space Used (MB)],

CONVERT(Decimal(15,2),ROUND((a.Size-FILEPROPERTY(a.Name,'SpaceUsed'))/128.000,2)) AS [Available Space (MB)]

FROM dbo.sysfiles a (NOLOCK)

 

Interestingly, we can see that there is a logical file name (Name) and a physical file name (Filename) for the database files and the log files.

 

It highlights a gotcha - the DBCC SHRINKFILE command (which @Rawns uses in his excellent scripts) take the logical SQL filename, instead of the physical filename. Normally it's the same, but watch out for it.

Edited December 12, 2011 by jinnantonnixx

[CadlaM]

Always good to read what some of you guys write about SIMS. Happened across this thread by accident and looked at SIMS dbs. I have Options set to Full Recovery model! They were only in here at end of summer getting me on new version of SQL, so going to change that. BUT. as ldf is 1.8GB and 3 times size of data file, just not sure which order to do things? Obviously Backup first (done every night). Rec model first , to reduce file size, or Shrink?

 

Then I'll tell you about my SOLUS 3 / firewall troubles :-(