[sims] Transaction Log File

[caffrey]

I've been reading a few threads on here about the SIMS LDF file growing huge, just noticed ours has grown to over 50GB in size. Is there a compiled FAQ on correcting this ? The information seems quite scattered. I generally don't like touching the SIMS install, but I am comfortable with SQL

[jinnantonnixx]

That is a fair sign that there's something wrong with your backups.

Describe your backup routines before going any further.

[jinnantonnixx]

*** Usual caveats apply here - if you're not confident with SQL queries or code, don't proceed. If you break it, you get to keep the pieces but that's all ***

 

 

If your OK with SQL and scripts, this SQL code will display your backups. This will give you a very complete view on your backups.

The results are ordered by database name and last backup date (descending)

 

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
--- AND sysdb.name = 'SIMS%'
ORDER BY sysdb.name, bkup.backup_finish_date desc

[jinnantonnixx]

*** Usual caveats apply here - if you're not confident with SQL queries or code, don't proceed. If you break it, you get to keep the pieces but that's all ***

 

 

Next, we can move to your log file. This SQL script will display information about your log file. The key items here are the Currently Allocated Space (the space within the file on disk that the log file is actually using for its operation) and the Space Used (the size of the file allocated for the log file on disk).

 

I'm going to make a guess and say that the log file size in use is nowhere near the log file size allocated on disk. If this is the case, and your backups are functioning correctly and proven to work, the log file can be re-sized to a more sensible value.

 

use [type the name of your sims database here]
go

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)

Edited March 25, 2015 by jinnantonnixx

[caffrey]

Cheers,

 

mdf is 886MB and the ldf is 52736MB,

 

The backups are fine and one ran last night, however they are set to *full* and they are growing in size at a rate of knots (28/02/2015 was 828MB and last nights was 883MB)

[jinnantonnixx]

What value do you have in the column "Space Used (MB)" for your log file (ldf) when you run the script in post #4?

[caffrey]

It's at 29585.98MB

[jinnantonnixx]

That's what we can technically refer to as 'big'.

 

Your log file is not being truncated by your backup system.

 

So, the next question is how do you backup your database? Homebrew script or using a package such as Veritas?

 

What sort of backup regime do you intend? Daily simple backups, weekly Fulls with daily or 6 hourly differential, etc?

Edited March 25, 2015 by jinnantonnixx

[caffrey]

The backup was setup by the SIMS engineer that came to site, I've had zero input except modify the backup script to create folders based on date for incremental. It's this :-

 

Set dd=%DATE:~0,2%
Set mm=%DATE:~3,2%
Set yyyy=%DATE:~6,4%

set var=%dd%-%mm%-%yyyy%

md C:\BACKUP\%var%

"C:\SimsSql\MSSQL11.THSSIMS\MSSQL\Binn\DbAttach.exe" /BACKUP /AUTO /SERVER=*******\***** /DATABASE=****** /USER=******* /PASSWORD=******** /PATH=C:\BACKUP\%VAR%\SIMSBACKUP.BAK"

[jinnantonnixx]

OK. So you do a daily full backup of the SIMS database. You don't back-up the log file separately. If you need to restore the database, you just restore a particular day's database and don't restore log file backups afterwards - is that correct?

 

Can you run this SQL script and see what recovery model is set for your SIMS database?

 

SELECT name AS [Database Name],
recovery_model_desc AS [Recovery Model]
FROM sys.databases

[caffrey]

It's set to FULL, only had to restore once using dbattach but it worked

[jinnantonnixx]

Aha. The plot thins.

 

You're doing SIMPLE backups, but your recovery model is set to FULL.

 

You'll need to change your recovery model to SIMPLE.

 

You must be 100% totally and absolutely sure that nobody is using SIMS when you do this. If you get your database stuck in single-user mode - and you're not the single user - well, it can be a real work-out for the bowels.

 

Here's how you do it. The easiest way is found under the "Using SQL Server Management Studio" bit.

https://msdn.microsoft.com/en-GB/library/ms189272.aspx

Edited March 25, 2015 by jinnantonnixx

[jinnantonnixx]

When you've done that, you'll need to wait for your next backup to run. This will truncate the logs, so running running the script in post #4 should give you a much smaller value for the "space used". The physical file size will remain unchanged.

 

Give it a few more days to make sure that the log space in use isn't increasing. If so, you've got a stable system. Small fluctuations are fine, but you shouldn't see an upward trend.

 

Now you can resize the physical log file. You'll need to give yourself some breathing room (as the log file will increase dramatically when you do a SIMS upgrade, for instance), so let's say your baseline log file in-use size is 400Meg. Setting the physical log file to something like 2 Gigs will give you plenty of room. Don't cut your cloth tight and rely on auto-grow - that's not a management strategy.

 

https://technet.microsoft.com/en-us/library/ms190757%28v=sql.110%29.aspx?f=255&MSPPError=-2147217396

Edited March 25, 2015 by jinnantonnixx

[caffrey]

Cheers for the help, I'll follow that and report back if everything is okay, just makes me wonder why it was set up like this in the first place.....

 

BTW both of your links are the same ?

 

Also can I use the SIMS ShrinkDBLog batch file to this ?

Edited March 25, 2015 by caffrey

[jinnantonnixx]

Cheers for the help, I'll follow that and report back if everything is okay, just makes me wonder why it was set up like this in the first place.....

 

BTW both of your links are the same ?

 

Fixed the link.

 

Normally SIMS databases are set up for SIMPLE recovery mode. Odd to see it accidentally set to FULL.

[caffrey]

What's the best way to make sure no-one is using SIMS ? I ran sp_who and I can see SIMS.net users, but I'm guessing I'll need to shut down PARS too as that has several instances, or is it best to just take the database offline ?

[jinnantonnixx]

The SIMS software client uses transient connections to the database. That is, the connection is made when needed, then dropped, even if the user is logged in and looking at a page of data.

 

So it's tricky to be absolutely sure that nobody's in. SIMS could have designed the system so a logged-in flag is set on login, and released on log out.

But they didn't, so we have to improvise.

 

This script will give you a snapshot of the connections to SIMS at any one time.

 

use master;

select 	upper (sysprocesses.loginame) [sIMS User],
sysprocesses.hostname [Machine],
sysprocesses.dbid,
--	sysdatabases.dbid,
sysdatabases.name [Database]	
from sysprocesses

join sysdatabases on sysprocesses.dbid = sysdatabases.dbid

where sysdatabases.name like 'sims%'     --change this to your database name

order by sysdatabases.name  -- works for multihosting systems

[caffrey]

Thanks again, some handy scripts there. I'll do the recovery model change after home time this evening hopefully should be okay

[jinnantonnixx]

@caffrey -

 

 

I made a mistake over my caution when changing the recovery model: I've checked, and apparently changing the recovery model is OK while the database is in use. I must have been thinking of something else. Never mind, though, that's good news.

 

That said, you'd still be wise to make the change out of hours.

Edited March 25, 2015 by jinnantonnixx

[matt40k]

So it's tricky to be absolutely sure that nobody's in. SIMS could have designed the system so a logged-in flag is set on login, and released on log out.

 

LOL!! You sir are leaving in a dream world. They did use to have such a flag, but it was woefully inaccurate because hardly anyone ever logged out cleanly!! IE you had teachers listed who actually just pulled the cable and ran out the door come 3pm so you would be hunting round the building trying to find a ghost to kick them off SIMS. Also the list of "active" SQL users should be pretty accurate nowadays as SIMS now has alerts\messages so the client, when left open, will query the SQL server every few mins

 

EDIT:

A maintenance mode would have been good (restricted users). Also avoid single user mode like the plague, in between your set single user mode and your next statement a client can come steal that single connection, especially when you have hundreds of clients and third party apps connecting up automatically.

Edited March 25, 2015 by matt40k

[jinnantonnixx]

I like my dream world. It's great. Everything is designed well. The passport controls are fairly strict, mind you.

[matt40k]

Yer, change control is key to a good system. I think people fear it because it highlights mistakes, but how can you learn from your (or others) mistakes if you just ignore them.

 

One thing worth mentioning is report it back to your SIMS Support team \ who set it up - basically was the recovery model not set correct because they didn't use the migration tool\whatever? IE is it user error, which is impossible to escape. Or is it a bug in dbattach? Or Capita migration tool - ie something that can be fixed and needs to be fixed.

[caffrey]

It was a fresh new install on a fresh new server, we were an E1 school before, engineer came to site and installed it clean - I had no input at all.

[matt40k]

@PhilNeal - might want to get your folks to check the new SIMS db creator tool sets the recovery mode to SIMPLE by default.

[Cache]

Saying that, our database somehow changed from Simple to Full for no apparent reason which I didn't realise until we were having some performance problems and I started looking at it in more detail.

 

No idea when it changed but it was definitely Simple when it was migrated across.