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

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

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

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

[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 :-(

[Rawns]

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 :-(

Changing the recovery model of the database won;t actually do anything to tour log file size. It will only stop it from growing further. I've also found that if you truncate the database without changing the recovery model to simple, it barely makes any difference to the size of the log file either. Therefore, I would backup your database first of all, change the recovery model to simple mode and then truncate the database either using SQL management studio or using the script I posted above.

[CAM]

Our server filled it's hard disk up and the LA pointed to a file (didn't say which) growing huge as people weren't logging off overnight and not allowing SIMS to do it's cleaning up. Could that be the cause?

[CadlaM]

Changing the recovery model of the database won;t actually do anything to tour log file size. It will only stop it from growing further. I've also found that if you truncate the database without changing the recovery model to simple, it barely makes any difference to the size of the log file either. Therefore, I would backup your database first of all, change the recovery model to simple mode and then truncate the database either using SQL management studio or using the script I posted above.

Thanks Rawns, and CAM too. Carried out the above and ldf is now down to "tiny". That's a technical term. File size reduced after another backup by SIMS system manager after the truncate. Don't get the problem of users not logging of, we use Ranger to log EVERYONE off. Hee Hee.

Now, about Solus 3 and firewalls.....

[vikpaw]

Our server filled it's hard disk up and the LA pointed to a file (didn't say which) growing huge as people weren't logging off overnight and not allowing SIMS to do it's cleaning up. Could that be the cause?

Yep it's usually the ldf. do as above and check the file, and maybe change recovery model, or just regularly backup and shrinkfile. I don't think SIMS does any 'cleaning up'. I have users staying logged in all the time, though am in Simple mode. The only issue with users logged in, is that they keep a connection open and the bloomin' homepage keeps making calls to the server. I guess in theory, this could always be sending transactions even during the backup period. EDIT: But they are only reports that are running, it's not like anyone is editing data. Or are they? I have had to set a rule that all users must be logged out by midnight, so if i need to, i can kick them off their computers and do any maintenance i want.

[jinnantonnixx]

Hey Vik, I'm sure you have this script (or some variant), but this tells you who (or what) is active on the system.

It's not the same as who's left their workstations on because the SIMS application creates an SQL connection on demand, then deletes that connection so it can never be 100% accurate, but it's something.

It works on multi-hosted servers, too, provided your database names start with 'sims'. Change as needed.

You can schedule it to make a 'naughty list' of who to blame when your upgrades go wrong.

use master; 
select 	sysprocesses.loginame as [Logged-in user],	sysprocesses.hostname as [Machine name],	sysdatabases.name as [Database name] from sysprocesses 
join sysdatabases on sysprocesses.dbid = sysdatabases.dbid where sysdatabases.name like 'sims%' order by sysdatabases.name