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.
- 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
- 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: