Sharepoint Config Database logs are getting too big, how can i shrink them.
Resolution:
1. Start Notepad. To do this, click Start, point to All Programs, point to Accessories, and then click Notepad.
2. 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
3. Open an elevated command prompt. To do this, follow these steps:
1.
Click Start, click All Programs, click Accessories, and then right-click Command Prompt.
2.
Click Run as administrator. Note If you are prompted for an administrator password or for confirmation, type the password or provide confirmation.
4. At the command prompt, copy and paste (or type) the following command, and then press Enter:
sqlcmd -S \\.\pipe\mssql$microsoft##ssee\sql\query -E -i c:\logshrink.sql
Note This script creates two backup files (before.bkf and after.bkf) in C:\windows\temp.
