[sims] SIMS LDF growing even though it's in SIMPLE mode?

[Rawns]

Is there any reason the SIMS log file would grow even though the database is in simple recovery mode? We have a site where SQL is sucking up almost all of the 16Gb of memory on the server, and it also seems to be causing intermittent CPU spikes. I looked at it last week and the SIMS LDF file was over 1Gb so I freed up the space in the lolg file and it was back at a nice 1Mb. Performance seemed to improve again, but I'm checking it a week later and again, it's sucked up almost all the memory and the LDF file has grown to almost 3Gb and again, it's still in simple recovery mode! The CPU spikes look like they are back as well, and it's got me baffled! I know SQL has a tendency to grab as much memory as it needs but this server is a beast and I can't imagine that it's used so heavily that it needs all of the 16Gb! Edited May 30, 2012 by Rawns

[vikpaw]

@jinnantonnixx probably knows all about this little known fact, but i think this may help: recovery mode = simple but ldf grows

 

I'd say, look for what is needing so much space, perhaps some very complex actions, reports or something. You should be able to schedule a nightly shrink db command to help it out, and perhaps alter the growth rate. I'm not sure setting a NO grow option is helpful as i don't know what happens if there is no space, it might reject the transaction.

[jinnantonnixx]

It's inextricably linked to your backup (or lack thereof).

 

In simple mode (and all other modes), the log file grows with every transaction. When you run a backup, the log file is truncated and space within the physical log file is reused.

 

Contrary to popular opinion, the log file is used even in simple mode. It cannot be used as part of a restore, but it's vital for the consistency of the database when the server comes back online after, say, a crash or power cut.

 

Tell us how you backup - if you say stop SQL service and copy the files, I will come over and set fire to your trousers.

Edited May 30, 2012 by jinnantonnixx

[jinnantonnixx]

Some succulent nuggets in this thread:

 

http://www.edugeek.net/forums/mis-systems/86996-massive-sim-ldf-file-anyone-else-had-theres-suddenly-grow.html

[jinnantonnixx]

SQL performance and memory usage is tricky to troubleshoot from the server's perfmon tool. Far better to use SQL's Activity Monitor. From SQL Server Manager, right-click your server in the object explorer tree view and choose Activity Monitor. Leave it for a few moments the look at the charts and tables to see if anything stands out.

 

Look in the Data File I/O table and look for high values in MB/sec and response time.

 

Also look in Processes and see if you can find anything with a 'Blocked by' - this indicates a delay caused by a contention.

Edited May 30, 2012 by jinnantonnixx

[User3204]

I dunno about you, but at this time of year all our clerical staff are all putting on the new year 7s for September.

 

Plus we also have two years on internal exams, and this means we have about 140 cover lessons per day for a week.

 

I had to go through the process last week, as mine had gotten up to 19gb LDF, while my MDF was only 6.5gb. I ran through all the usual stuff, and my MDF increased in size, but the LDF went down to 1mb, and in one week it's gotten back up to 7mb.

[Rawns]

It's inextricably linked to your backup (or lack thereof).

 

.....

 

Tell us how you backup - if you say stop SQL service and copy the files, I will come over and set fire to your trousers.

 

I assure you there is a backup mechanism in place. It's all scripted but essentially it's a SQL script to transfer all logins, dbAttach for the actual backup, then a utility to compress the bak files.

[Mcshammer_dj]

do you use anything like PARS??

[Rawns]

do you use anything like PARS??

 

Nope, the school use SIMS .net.

[localzuk]

Can you post your backup script here? As @jinnantonnixx says, if you have a backup system in place it should be truncating the ldf file.

[jinnantonnixx]

Use this bit of code to check the backup history for a particular database:

 

DECLARE @mydatabase as varchar(100)

set @mydatabase = 'your database name goes here'

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 MB' ,
cast((bkup.backup_size /1073741824) as decimal (9,2)) as 'Size GB',
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
AND sysdb.name = @mydatabase
ORDER BY sysdb.name, bkup.backup_finish_date desc

 

I have my lighter at the ready....

Edited May 31, 2012 by jinnantonnixx

[Rawns]

Use this bit of code to check the backup history for a particular database:

 

DECLARE @mydatabase as varchar(100)

set @mydatabase = 'your database name goes here'

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 MB' ,
cast((bkup.backup_size /1073741824) as decimal (9,2)) as 'Size GB',
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
AND sysdb.name = @mydatabase
ORDER BY sysdb.name, bkup.backup_finish_date desc

 

I have my lighter at the ready....

 

Thanks for this @jinnantonnixx. Just ran it and I can see every backup for the previous 60 days, and they are all full backups.

@localzuk, first we transfer the logins:

 

osql -S%COMPUTERNAME%\SIMS2008 -Usa -P%SA_PASSWORD% -n -Q"exec %simsdb%.sims.db_p_transfer_login"

 

... then back up using DBAttach:

 

"%SIMSDrive%\%SQLPath%\Binn\dbattach.exe" /BACKUP /AUTO /SERVER=%COMPUTERNAME%\SIMS2008 /DATABASE=%simsdb% /USER=sa /PASSWORD=%SA_PASSWORD%

 

Then the bak files are compressed with @matt40k's compression utility. It's how we do it in every school in the LA.

[jinnantonnixx]

away from office now, on phone.

next thing to check is how much of the log file is actually used.

vik knows which script I mean.

[vikpaw]

away from office now, on phone.

next thing to check is how much of the log file is actually used.

vik knows which script I mean.

 

I believe my esteemed colleague is referring to this:

 

use sims;

dbcc showfilestats


SELECT    Name, Filename,


CONVERT(Decimal(15,2),ROUND(a.Size/128.000,2)) [Currently Allocated Space (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)

 

You can get the same numbers if you right click on db, tasks, shrink files, then flip between the two files. But the code is more elegant.

I'm not sure, but i think if you have a problem with shrinking ldf, then in addition to the normal backups you need to do a transaction log backup. This then allows it to be shrunk. However, this may be a Full Backup mode technique.

 

I'm pretty sure in just Simple mode, I run the shrink file and it works.

[jinnantonnixx]

That's the one. What stats do you get, @Rawns?

[vikpaw]

My current system has 4.3GB allocated MDF, but 3.6GB used. LDF: 1.3GB allocated, only 13MB used. )

[Rawns]

That's the one. What stats do you get, @Rawns?

 

For the dat file, 4557.75Mb currently allocated, 3422.75Mb used, 1135Mb available.

For the log file, 19.63Mb currently allocated, 11.27Mb used, 8.36Mb available.

[vikpaw]

Did you shrink it already?

[jinnantonnixx]

those stats look fine.

[Rawns]

The SIMS reindex patch was ran last night on site so that's why the log is smaller again.