[sims] Sims .ldf at 46GB

[Jobos]

Looking through the sql data folder and I see the sims .mdf at 760MB but the .ldf is at 46GB How on earth did it get this big!

 

The databases are backed up each night and I've tried backing up with dbAttach but the .ldf does not shrink.

 

What can I do to fix this?

[JJonas]

I think these are the log files. I had a similar problem with Sophos log files yesterday I was able to shrink them with Microsoft SQL Server Management Studio

 

I can give instructions if you like, but im no expert (When I shrunk mine it was very much fingers crossed hope it works)

Edited December 12, 2012 by JJonas

[glennda]

If you open Management studio go to properties of the database and see what the recovery is set to, set this to simple and then run a backup.

[Rawns]

Check to see if the SIMS database is in FULL recovery mode. Connect to the instance using management studio and open the properties of the SIMS database and go to the Options section. If it is, put it into SIMPLE mode. You can then right click the database and go to tasks and shrink the log file where you can release the used space. This should take it back down to around 1Mb in size.

[JJonas]

^ I can confirm this works its what I did to my Sophos logs yesterday

[jinnantonnixx]

It's almost certainly a problem with your backups at some time. Maybe a while ago, but could still be current.

 

Your log file will grow continuously until you back up your database. This is by design.

 

If you use simple recovery mode, a backup will truncate the log file. This will allow the physical space the log file occupies to be re-used. It will not shrink the log file - it will continuously recycle the physical file space. You can shrink the log file from the SQL console. Don't shrink it to the minumum value, as it will almost instantly need to auto-grow, hitting the performance a little. This auto-grow will be 10% of the original value, which will soon not be enough, and will need another 10% auto-grow. Rinse and repeat. Far better to resize the log file to something like the same size as your database file.

 

If you're OK with SQL queries, you can run this query which wlll tell you the status of your backups.

 

SELECT sysdb.name, bkup.description, bkup.backup_finish_date,
case
when type='D' then '** FULL **'
when type='I' then 'DIFFERENTIAL'
when type='L' then 'LOG'
end as Backup_Type,
(STR(ABS(DATEDIFF(day, GetDate(),(backup_finish_date))))) as 'Days_Ago',
ceiling(bkup.backup_size /1048576) as 'Size Meg' ,
cast((bkup.backup_size /1073741824) as decimal (9,2)) as 'Gig',
server_name, sysdb.crdate
,datediff(minute, bkup.backup_start_date, bkup.backup_finish_date) as 'Mins'
,cast(cast(datediff(minute, bkup.backup_start_date, bkup.backup_finish_date)
as decimal (8,3))/60 as decimal (8,1)) as 'Hours', first_lsn, last_lsn, checkpoint_lsn
FROM master.dbo.sysdatabases sysdb LEFT OUTER JOIN msdb.dbo.backupset bkup ON bkup.database_name = sysdb.name
where backup_finish_date > DATEADD(DAY, -60, (getdate())) -- Last 60 days

ORDER BY sysdb.name, bkup.backup_finish_date desc

Edited December 12, 2012 by jinnantonnixx

[Rawns]

It's almost certainly a problem with your backups at some time. Maybe a while ago, but could still be current.

 

Your log file will grow continuously until you back up your database. This is by design.

 

If you use simple recovery mode, a backup will truncate the log file. This will allow the physical space the log file occupies to be re-used. It will not shrink the log file - it will continuously recycle the physical file space. You can shrink the log file from the SQL console. Don't shrink it to the minumum value, as it will almost instantly need to auto-grow, hitting the performance a little. This auto-grow will be 10% of the original value, which will soon not be enough, and will need another 10% auto-grow. Rinse and repeat. Far better to resize the log file to something like the same size as your database file.

 

If you're OK with SQL queries, you can run this query which wlll tell you the status of your backups.

 

SELECT sysdb.name, bkup.description, bkup.backup_finish_date,
case
when type='D' then '** FULL **'
when type='I' then 'DIFFERENTIAL'
when type='L' then 'LOG'
end as Backup_Type,
(STR(ABS(DATEDIFF(day, GetDate(),(backup_finish_date))))) as 'Days_Ago',
ceiling(bkup.backup_size /1048576) as 'Size Meg' ,
cast((bkup.backup_size /1073741824) as decimal (9,2)) as 'Gig',
server_name, sysdb.crdate
,datediff(minute, bkup.backup_start_date, bkup.backup_finish_date) as 'Mins'
,cast(cast(datediff(minute, bkup.backup_start_date, bkup.backup_finish_date)
as decimal (8,3))/60 as decimal (8,1)) as 'Hours', first_lsn, last_lsn, checkpoint_lsn
FROM master.dbo.sysdatabases sysdb LEFT OUTER JOIN msdb.dbo.backupset bkup ON bkup.database_name = sysdb.name
where backup_finish_date > DATEADD(DAY, -60, (getdate())) -- Last 60 days

ORDER BY sysdb.name, bkup.backup_finish_date desc

 

Will this report on the status of your backups if they are taken with dbAttach?

[jinnantonnixx]

Will this report on the status of your backups if they are taken with dbAttach?

 

dbattach should hook into the BACKUP routines of SQL, so yes, it will.

[Jobos]

Thanks everyone!

 

The recovery mode was set to full so now after a backup the .ldf is 1.5MB.

 

@jinnantonnixx

Where is the setting for the minimum size of log file?

[jinnantonnixx]

Using SQL Server Manager, expand the 'Databases' tree, right-click your database and choose 'Properties'

In the left column, select the 'Files' page, you'll see the see the settings for the files in use by your database, normally one for the database file and one for the log file. There's an 'initial size' field; here is where the size is set. Too small a value means that auto-grows frequently take place, this isn't ideal.

[Geoff]

Yeah this is basic MSSQL DBA stuff. I'm surprised that Sims doesn't sort this out itself though? I know Capita take a dim view of people prodding the Database directly.

[pete]

Yeah this is basic MSSQL DBA stuff. I'm surprised that Sims doesn't sort this out itself though? I know Capita take a dim view of people prodding the Database directly.

 

SIMS does if you use the built-in backup routine. Most people don't use that if they've got full SQL, so the only time it might get triggered (and the log shrunk) is before an upgrade if someone isn't herding the database properly.

[vikpaw]

If you wanted to stay FULL, to get the file to shrink, you need to run a transaction log backup, after which the shrink command works. Just a normal backup doesn't cut it.

If you're staying SIMPLE mode, then you're good now.

You should change the growth setting from x % to y MB, to prevent it going exponential! Not sure if that was clear from Jinn's post.

In the old days, this was blamed on third party tools accessing the db, but they tend to behave themselves now.