I logged into a client’s machine today and saw this one sharepoint file was over 7 gigs in size. I am thinking this is one reason sharepoint has been pokey as of late. Some googling showed me that by default MS basically has the database in debug(which means every little thing is logged no matter what). That’s good for diagnostics but it makes the database get bloated to the point it kills performance. I found this MS page about the issue.
here are the instructions as noted in the article:
To reduce the size of the log file, use the following steps. A full server backup is recommended first.
1. Open notepad and Copy and paste the following text into notepad. Save the file as c:\logshrink.sql
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
2. Open an elevated command prompt and run the following command:sqlcmd -S \\.\pipe\mssql$microsoft##ssee\sql\query -E -i c:\logshrink.sql
I had to run it twice to get the file back down to a reasonable size.