Shrink ShareWebDB Log File on SBS 2008

There’s a well known issue on SBS 2008 servers where the SharePoint Config Log file continues to expand. This SQL log file can rapidly grow in size, eating up valuable disk space on your system C: drive.

I first blogged about this back in December 2008 (view blog).

I posted a second blog post 4 years late in December 2012 (view blog) which provided the recommended Microsoft fix (KB 2000544) to truncate the log file, creating a batch command file and an associated SQL command file to truncate this log file.

I only have a few SBS 2008 servers still installed out in the field. But recently I discovered that there is another similar SQL log file that can grow in size: ShareWebDB_log.ldf file.

On this particular server, the ShareWebDB log file had grown to over 200GB in size. Ouch! Ouch! Ouch!

image

The solution is to mimic the two files created for the SharePoint log file solution, but have it truncate the ShareWebDB log file instead.

STEP 1: Create the SQL command file

Download the SQL file from my web site (logshrink2.sql.txt), save it to the root of your C: drive, and then rename it to logshrink2.sql

declare @ConfigDB varchar(255);
declare @ConfigDBLog varchar(255);
declare @ConfigDBCmd varchar(255);
select @ConfigDB =  name from sys.databases where name like ‘ShareWebDb%’;
set @ConfigDBCmd = ‘BACKUP database [‘ + RTRIM(@ConfigDB) + ‘] to disk=”C:\windows\temp\before2.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 ‘ShareWebDb_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\after2.bkf”’;
execute(@ConfigDBCmd);
go

STEP 2: Create the DOS batch command file

Next, download the DOS batch command file from my web site (logshrink2.cmd.txt), save it to the root of your C: drive, and then rename it to logshrink2.cmd

dir c:\windows\sysmsi\ssee\mssql.2005\mssql\ShareWebDb* /s
pause
sqlcmd -S \\.\pipe\mssql$microsoft##ssee\sql\query -E -i c:\logshrink2.sql
pause
dir c:\windows\sysmsi\ssee\mssql.2005\mssql\ShareWebDb* /s
pause

STEP 3: Run the command file with administrator rights

Finally, open up File Explorer, right click on the logshrink2.cmd file, and click to “Run as administrator”

RESULT: Here is a screen shot of running this on the server with a 200GB log file:

image

Leave a Reply