Wednesday 13 February 2013

SBS 2008: SharePoint Configuration Log File Huge

We have a client setup that is getting a bit constrained on storage:

image

One of the culprits was the above SharePoint Configuration log file.

The following KB has the fix for us:

We need to copy and paste the following into NotePad on the affected SBS:

declare @ConfigDB varchar(255);
declare @ConfigDBLog varchar(255);
declare @ConfigDBCmd varchar(255);
select @ConfigDB = name from sys.databases where name like 'SharePoint_Config_%';
set @ConfigDBCmd = 'BACKUP database [' + RTRIM(@ConfigDB) + '] to disk=''C:\windows\temp\before.bkf''';
execute(@ConfigDBCmd);
set @ConfigDBCmd = 'use [' + RTRIM(@COnfigDB) + ']';
execute(@ConfigDBCmd);
set @ConfigDBCmd = 'BACKUP LOG [' + RTRIM(@ConfigDB) + '] WITH TRUNCATE_ONLY';
execute(@ConfigDBCmd);
set @ConfigDBCmd = 'use [' + RTRIM(@COnfigDB) + ']';
execute(@ConfigDBCmd);
select @ConfigDBLog = name from sys.database_files where name like 'SharePoint_Config%_log';
set @ConfigDBCmd = 'use [' + RTRIM(@ConfigDB) + '] DBCC SHRINKFILE([' + RTRIM(@ConfigDB) + '_log],1)';
execute(@ConfigDBCmd);
set @ConfigDBCmd = 'BACKUP database [' + RTRIM(@ConfigDB) + '] to disk=''C:\windows\temp\after.bkf''';
execute(@ConfigDBCmd);
go

We then need to save the file as "LogShrink.sql" in a Temp folder on the server. In our case we saved it to C:\Temp

image

We encapsulate the full name of the file in quotes to force the .SQL extension on the file name.

Open an elevated command prompt and:

sqlcmd -S \\.\pipe\mssql$microsoft##ssee\sql\query -E -i c:\temp\logshrink.sql [Enter]

Once completed our log file was a paltry 1MB in size:

image

Philip Elder
MPECS Inc.
Microsoft Small Business Specialists
Co-Author: SBS 2008 Blueprint Book

Chef de partie in the SMBKitchen
Find out more at
www.thirdtier.net/enterprise-solutions-for-small-business/

Windows Live Writer

3 comments:

Anonymous said...

Brilliant.
From 29GB down 1to 1024KB on this groaning old SBS 2008 box, in minutes.
Golden script - concisely written up as always. SBS may be deceased, but this blog still aids and assists. Thanks Philip.

Binu Kumar said...

Excellent. Works beautifully :)

Garrett said...

Worked like a charm!