I am still encountering SBS 2008 servers where the Best Practices Analyzer (BPA) reports that the SharePoint SQL Log File is getting too large. There is a KB article that documents a script that will truncate the log file. I’ve created a zip file with the appropriate SQL command line plus a batch file to run it (see Part 1 below).
It is also recommend that after truncating the SharePoint database log file, that you set the recovery mode to “simple” for the database log file (see Part 2 below), and then you won’t need to use this command script again!
Part 1 – Running the command script
I created a zip file that contains both the SQL command line file that the KB article describes, plus a .cmd file that you can use to run the SQL command. In addition, my .cmd file will display the size of the SharePoint database log file both before and after.
Steps:
- Click here to download my zip file. (be sure to rename it from logshrink.z_i_p to logshrink.zip)
- Create a directory C:\Scripts
- Extract the contents of the zip file to the C:\Scripts directory
- Review and edit the .cmd file to adjust the location of the SharePoint database log file. I usually move SharePoint to a D:\ (Data) drive.
- Create a shortcut to the logshrink.cmd file and place it on your desktop
- To run, right click on the shortcut and click ‘Run as administrator’
Here is a screen shot of the results of running the script:
Part 2 – Switching Recovery Mode
Steps
- Open SQL Server Management Studio Express (using Run as Administrator)
- In the Connect to Server window, for the server name, enter: \\.\pipe\MSSQL&Microsoft##SSEE\sql\query
- Under Databases, locate SharePoint_Config_29c26fca…
- Right click on the above database, and click Properties
- Click on Options in the left frame, and in the right frame you will see that Recovery Mode is set to Full.
- Use the drop down option box and select Simple
- Click to save, and then exit SQL Server Management Studio Express
- Here’s a screen shot where you will change the recovery type from Full to Simple:
