27 June 2010

Shrinking the Sharepoint Config DB Log file

One of the SBS servers I look after showed a disk space issue. I had already disabled the WSUS log file, so was a bit puzzled where all the disk space had gone. Upon closer inspection using my favority Treesize tool, I found that the Sharepoint Configuration Database transaction log had grown to 11GB. Well the client uses Sharepoint a bit, but surely this was out of the oridinary on a 160MB DB data file.

I fired up SQL 2005 Express Managment Studio that is installed with SBS (make sure you run as Admin), but was initially unable to connect to the Windows internal database, as it is hidden from general view. However after a bit of googling I found that I had to connect using

\\.\pipe\MSSQL$MICROSOFT##SSEE\sql\query

to access the database. Next I wrote a quick script that shrinks the log file:

USE Master
GO
BACKUP LOG [SharePoint_Config_29c26fca-17b8-48c1-9704-b869932abcb6] WITH TRUNCATE_ONLY
GO
USE [SharePoint_Config_29c26fca-17b8-48c1-9704-b869932abcb6]
GO
DBCC SHRINKFILE([SharePoint_Config_29c26fca-17b8-48c1-9704-b869932abcb6_log],50)
GO


The Database name may vary from system to system (but appears the same for all SBS 2008 installations), so the above will need to be adjusted to the name of your sharepoint config database name.

While you are there, you may want to check and apply the same fix to the actual Sharepoint Web content database:


USE Master
GO
BACKUP LOG [ShareWebDb] WITH TRUNCATE_ONLY
GO
USE [ShareWebDb]
GO
DBCC SHRINKFILE([ShareWebDb_log],50)
GO


Happy Shrinking!