Shrinking SIMS LDF file

[MrWu]

Hi all

 

Capita has suggested I shrink the ldf file using their attached ShrinkDBlog utility

 

Can this be run live and do I need to kick users off SIMS first ?

 

 

Details they sent below , thanks all !

 

 

 

 

 

KB104766 How can you shrink the SIMS / SOLUS 3 / Discover / FMS LDF file?

KBA-455527-J2F1D3

Notes

How to shrink the SIMS LDF?

How to shrink the Discover LDF?

How to shrink the SOLUS 3 LDF?

How to shrink the FMS LDF?

ShrinkDBLog.bat SQL2008 : On Demand Data Tool - Re-Index / Shrink the LDF via a Batch-file

 

Workaround

On Demand Datafix Tool is available to resolve this issue. Please request this from your local support unit

- This tool is compatible with SQL 2008, 2012 and 2014.

- This can also be used for shrinking the Discover LDF.

- This can also be used for shrinking the SOLUS 3 LDF

 

Instructions for running ShrinkDBLog.bat

1) ShrinkDBLog.zip will need to be unzipped onto a local drive on the machine SQL is installed.

 

2) The batch file will need to be edited - right hand click select 'Edit'.

 

3) Edit the following line:-

 

osql -S %1 -d %2 -Usa -P -n -i'%temp%\shrink.sql'

The SA password needs to be added into the osql command line which comes immediately after the -P for example, if the SA password is PASSWORD the OSQL line will need to read as follows:-

 

osql -S %1 -d %2 -Usa -PPASSWORD -n -i'%temp%\shrink.sql'

4) Once this line has been edited, save the file and close it down.

 

5) Open up Command Prompt and then change the current directory to where the ShrinkDBLog.bat tool is stored, e.g.

CD C:\ShrinkDBLogFolder

IMPORTANT NOTE: If User Account Control (UAC) is in use on this machine, then you might need to run Command Prompt by right clicking on it and selecting the 'Run as Administrator' option.

 

6) And then type: 'ShrinkDbLog {Server}\{Instance} {DatabaseName}' into Command Prompt. A proper example is:

ShrinkDblog AdminServer\SIMS2008 SIMS

This will truncate the .LDF file and set the recovery model to 'Simple'.

 

If required, the ShrinkDBLog.bat tool can also be setup as a scheduled task to run on the server every-so-often, e.g. every couple of months.

[Edu-IT]

From memory it can be done live. But personally I would do it out of hours, when nobody is on. You could set it as a schedule task to run in the night after a full backup.

[dapaulio]

If your ldf are getting ridiculously large then I would look at your backups.

Are they working properly as sql integrated backup systems would normally shrink them once backup is complete. Eg DPM

If you backup any other way ensure you have a complete working backup.

In sql management suite

Click on sims database

Click on task then shrink

Drop down to log file and you will notice it will fully utilised

Press cancel

change the recovery model from full to simple

The ldf file would not have changed in size yet as this only creates white space in the file.

Repeat the shrink task but you will notice this time that the free space on the ldf file will be near 99%. The rest is white space

Continue this time to Shrink the log file.

Look at the ldf file size and you will notice it has shrunk this time releasing disk space.

Change the recovery model back to full. There is a reason why sims uses full and I can’t remember why. So best to restore it.

 

I have done this live before on other sql databases in the past but I know my backups works. May be best to do this out of hours as the backup itself will have to be done ideally in single user mode and maybe get your sims support provider in on it also

Edited October 26, 2017 by dapaulio

[Priya_Shah]

There is an alternative method which I prefer using and can be used while users are still logged in.

Open and login to SQL Management Studio, use the following for the query (depending on the names of your DB's) and Execute.

 

use Discover

go

alter database Discover

set recovery simple;

go

dbcc shrinkfile('discover_log', 1);

go

alter database Discover

set recovery full;

go

 

use sims

go

alter database sims

set recovery simple;

go

dbcc shrinkfile('361001_log', 1);

go

alter database sims

set recovery full;

go

 

use fms

go

alter database fms

set recovery simple;

go

dbcc shrinkfile('ccsfms_log', 1);

go

alter database fms

set recovery full;

go

Edited October 27, 2017 by Priya_Shah

[CJLane]

From memory it can be done live. But personally I would do it out of hours, when nobody is on. You could set it as a schedule task to run in the night after a full backup.

 

Sometimes when users are logged in, you can get error 1222 explaining resources already taken. We usually do it throughout the day. Usually takes 3-5 minutes to do, from logging into the server to finishing the process. Simple task when done through SQL Management Studio.