Yesterday you will have seen how I reduced the size of my SharePoint Config Log file on my SBS 2008 server from 10.9GB down to 1MB. While I was digging, I noticed that the ShareWebDB_Log.ldf file was also quite large at 2.8GB in size. I thought this was abnormal too – so I modified the script from the Microsoft KB article I found yesterday.
Here’s my modified script below
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\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
‘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\after.bkf”’;
execute(@ConfigDBCmd);go
I then saved the text above into a log file called ShareWebLogShrink.sql and ran the following command from an elevated command prompt
sqlcmd -S \\.\pipe\mssql$microsoft##ssee\sql\query -E -i c:\ShareWeblogshrink.sql
Once it was done, my ShareWebdb_log file was down to 1MB once more. Time will tell how it progresses in the future.
When i try run your script i get this error.
Msg 102, Level 15, State 1, Server SBSSERVER\MICROSOFT##SSEE, Line 5
Incorrect syntax near ‘`’.
Msg 102, Level 15, State 1, Server SBSSERVER\MICROSOFT##SSEE, Line 7
Incorrect syntax near ‘`’.
Msg 102, Level 15, State 1, Server SBSSERVER\MICROSOFT##SSEE, Line 9
Incorrect syntax near ‘”‘.
Msg 102, Level 15, State 1, Server SBSSERVER\MICROSOFT##SSEE, Line 12
Incorrect syntax near ‘`’.
Msg 911, Level 16, State 1, Server SBSSERVER\MICROSOFT##SSEE, Line 12
Could not locate entry in sysdatabases for database ” + RTRIM(@COnfigDB) + ”.
No entry found with that name. Make sure that the name is entered correctly.
Lee: change the quote marks to regular ones, not the angled ones.
Thanks for this! I was getting warnings about database size while migrating from SBS 2008 to 2011. I did some poking around and realized it was because of this log file. Ran your script, re-ran the preupgradecheck and it stopped complaining.
Perfect. Was upgrading SBS 2008 to 2011, exact problem, exact solution. Thanks.
(Remember to change both single and double quotes to the standard unangled ones, using, e.g., notepad and search & replace)
I’m having the same issues as Lee had. What qoutes marks need to change in other qoute marks.””””””””””“““ or do we have more of them?
Perfect I reduce 2 files from 3Gb to 1Mb. SharePoint_Config_773e233a-1a8d-44ff-911b-4d257168aec8_log.LDF and ShareWebDb_log.LDF
Thanks!!!
worked great , txs !