SBS 2008: SharePoint Configuration Log File Huge

[Xavier Mustin]

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

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

The following KB has the fix for us:

• Microsoft Knowledgebase KB2000544: SharePoint database log file is getting large in Windows SBS 2008

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

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: