About a year ago, we got a call from a client. Their email had stopped working. They are on SBS 2008, so we started troubleshooting. We restarted the stopped Information Store. It started up, then promptly shut down again. Upon further inspection we discovered that the main system drive was almost full.

When a server’s system drive gets full, things start shutting down. Usually the first to go is Exchange. Exchange 2003 would let you get as low as 13 Mb free before calling it a day, but Exchange 2007 (the version on SBS 2008) is a lot less tolerant. It starts dying with 10 Gb free.

Usually, we look at the folder on SBS that houses the Exchange databases, but these looked OK. Nothing overloaded and no mass of log files had gotten out of hand. (Many people will actually move the location of the Exchange databases to other drives if their server has more than one, but usually Exchange is left in the default location on C:.)

We started looking at directory size and noticed that the C:\Windows folder was abnormally large. Drilling down, we saw the C:\Windows\SYSMSI folder was huge, so we drilled down until we got to the C:\Windows\SYSMIS\SSEE\MSSQL.2005\Data folder. In this folder are a variety of standard SBS 2008 Sharepoint and WSS SQL files. The transaction log files (.LDF) were huge. As many of you know, SQL transaction log files can grow eternally unless a backup is run, at which time, the transactions are committed to the database and the log files are truncated. Apparently, someone at Microsoft forgot to build in a periodic backup program for Sharepoint and other internal SQL databases.

Searching on the web turned up KB2000544. This article describes the phenomenom and gives a script that will issue a SQL backup command which will shrink the log file. Unfortunately, this script affects only one of four or more db’s in that folder.

I have modified that script and have listed it below. This will clean up four of the largest log files. If you are actively using Sharepoint for file storage and content, this should help as well. If your drive is completely full, you may need to modify the location of the ‘before’ and ‘after’ bkf files, say, to an external USB drive instead of using the Windows temp folder.

Just copy the four blocks of the script and paste it into a notepad file called c:\logshrink.sql. Make sure that notepad doesn’t call it logshrink.sql.txt because that will cause the script to fail. Once that is done, open up an elevated command prompt (right click the command prompt icon in the Start menu and click Run as Administrator) and type (or paste) the following in the command box: sqlcmd -S \\.\pipe\mssql$microsoft##ssee\sql\query -E -i c:\logshrink.sql

After running this script, be sure to go into c:\Windows\temp and delete the newly created bkf files and while you are there you may as well delete everything else in that folder, too. Remember, that there are usually a couple of files in that temp folder which are actively open, so just skip over them.

CAUTION: Make sure you have a good backup of your server before you start and also be sure that you are actually in the temp folder before you delete everything.

I hope this helps get you past a really sticky situation with your SBS 2008 servers.

The script follows…

Randy

Start Snip —>

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\beforeCFG.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\afterCFG.bkf''';
execute(@ConfigDBCmd);
go
declare @ConfigDB varchar(255);
declare @ConfigDBLog varchar(255);
declare @ConfigDBCmd varchar(255);
select @ConfigDB = name from sys.databases where name like 'SharePoint_AdminContent_%';
set @ConfigDBCmd = 'BACKUP database [' + RTRIM(@ConfigDB) + '] to disk=''C:\windows\temp\beforeAC.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_AdminContent_%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\afterCAC.bkf''';
execute(@ConfigDBCmd);
go
declare @ConfigDB varchar(255);
declare @ConfigDBLog varchar(255);
declare @ConfigDBCmd varchar(255);
select @ConfigDB = name from sys.databases where name like 'ShareWeb%';
set @ConfigDBCmd = 'BACKUP database [' + RTRIM(@ConfigDB) + '] to disk=''C:\windows\temp\beforeSW.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 'Shareweb%_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\afterSW.bkf''';
execute(@ConfigDBCmd);
go
declare @ConfigDB varchar(255);
declare @ConfigDBLog varchar(255);
declare @ConfigDBCmd varchar(255);
select @ConfigDB = name from sys.databases where name like 'WSS_Content%';
set @ConfigDBCmd = 'BACKUP database [' + RTRIM(@ConfigDB) + '] to disk=''C:\windows\temp\beforeWSSC.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 'WSS_Content_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\afterWSSC.bkf''';
execute(@ConfigDBCmd);
go
declare @ConfigDB varchar(255);
declare @ConfigDBLog varchar(255);
declare @ConfigDBCmd varchar(255);
select @ConfigDB = name from sys.databases where name like 'WSS_Search_%';
set @ConfigDBCmd = 'BACKUP database [' + RTRIM(@ConfigDB) + '] to disk=''C:\windows\temp\beforeWSSS.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 'WSS_Search_%_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\afterWSSS.bkf''';
execute(@ConfigDBCmd);
go

 

<—- End Snip

 

Comments are closed.