Appling Patches 22573 and 20647 against the SIMS database and shrink Log

[MrWu]

Hi all

 

Been told by Capita to run these to improve performance. anyone have experience with this? As far as I know not run on the SIMS db at all (the SIMS MDB is 28Gb and LDF is 13Gb)

 

Anyone know why I need two indexing patches? Do they take a long time to run, also do I need to put the SIMS db in single user mode to run the shrink log utility?

 

Thanks as always

 

Wil

[LosOjos]

Open SQL Server Management Studio, go to DB properties, options, set recovery model to simple.

 

Right click DB, Tasks > Shrink > Files. File Type: log.

 

Will shrink the log files and improve performance.

[siuko]

I have just checked our Sims and the sizes are

 

Sims.mdf 9.31Gb

Sims.ldf 93.2Gb!!!

 

Is the Shrink safe to do or should I get the Sims patch?

[Norphy]

If your log file is that big, that indicates that a SQL aware backup has not been run on the database. When a proper backup is taken, the log file is truncated and returned to a more manageable size.

 

Attempting to shrink it will likely make no difference as there will probably be changes recorded in the log file which haven't been committed to the main MDF file. I would highly recommend against applying the patch as this indicates that a backup hasn't been run since forever.

 

The quickest way to get around this is to set up a backup inside SQL Server Management Studio and saving it somewhere, preferably on another server. Make sure you check the option to truncate the log. If the log file doesn't shrink, you can then run a manual shrink on the file and set SQL to recover the space to the filesystem.

[siuko]

I've just checked and the backups are done daily using a batch file that contains

 

sqlcmd -SSIMSSERVER2014\sims2012 -E -i E:\Simsbackup\backupsims.sql

 

and inside the backupsims.sql it has

 

DECLARE @tday VARCHAR(100)

DECLARE @file VARCHAR(100)

DECLARE @zip VARCHAR(150)

select @tday = DATENAME(dw, GETDATE())

set @file = 'E:\Simsbackup\' + @tday + '_' + '_sims_Daily_Backup.bak'

exec sims.sims.db_p_transfer_login

backup database sims to disk = @file with format

set @zip = 'del ' + @file + '.zip ';

EXEC xp_cmdshell @zip;

set @zip = 'E:\Simsbackup\7za.exe a ' + @file + '.zip ' + @file;

EXEC xp_cmdshell @zip;

set @zip = 'del E:\Simsbackup\*.bak';

EXEC xp_cmdshell @zip;

 

It was setup by someone else quite some time ago - is this not the best way to do it?

Edited September 26, 2017 by siuko