Shrinking an already ginormous SharePoint database transaction log

Last month, I wrote about how to avoid having ginormous transaction logs within SharePoint databases. But what if it’s too late, and your files are too large? This “ginormousity” (yes, I made up this word) would be discovered by viewing the SharePoint databases in SQL Server, checking the size of your SQL Server databases data file (.mdf) and making sure it’s about the expected size -- only to find that when you look at the associated transaction log file (.ldf) it is significantly larger than the data file.

If left alone, this ginormous transaction log file will continue to grow until it reaches the maximum size set by the SQL Server DBA, or until the physical drive containing the transaction log (.ldf) runs out of disk space. If either situation occurs, there is no room for transactions to be added to the transaction log, so users cannot perform any insert, update or delete actions in SharePoint.

This problem arises because every SQL data modification first is written to the transaction log and then to the data file, but being that the transaction log has no room to hold any new changes, the write-to-the-transaction log process cannot occur, and the modification fails. It also means you haven’t read my blog article on how to avoid ginormous transaction logs, and now have to take the steps necessary to shrink the transaction log (.ldf) file. 

You can use the following steps to shrink the transaction log file to a reasonable size (then be sure to implement the steps in my previous blog post to prevent this from happening again).

Begin by logging in to the SQL Server as a database administrator that is hosting the SharePoint databases, and then open SQL Server Management Studio (SSMS). Be sure that you are connecting to the instance of SQL Server that contains the SharePoint content databases.

Locate the database that has the ginormous .ldf file, and use either of the following sets of steps to shrink the transaction log file down to a reasonable size:

Using the Transact SQL ALTER DATABASE statement to shrink the transaction log file

Open SQL Server Management Studio (SSMS) and click Query Analyzer to open the Query Analyzer window. Type the following commands sequentially and execute them individually. (Note: The text to the right of each command explains the purpose of the command.)

1. USE SharePoint_Content_DB. Connect to the database that you want to shrink.
2. ALTER DATABASE SharePoint_Content_DB SET RECOVERY SIMPLE. Set the recovery model to SIMPLE.
3. CHECKPOINT. Issue a CHECKPOINT to purge out the inactive transactions.
4. DBCC SHRINKFILE (SharePoint_Content_DB_LOG, 5). Shrink the transaction log file to a reasonable size.
5. ALTER DATABASE SharePoint_Content_DB SET RECOVERY FULL. Set the recovery model back to FULL.

Using the SSMS GUI to shrink the transaction log file

Open SQL Server Management Studio (SSMS).

1. Expand the Databases node and expand User Databases.
2. Right-click the database name that you want to shrink, and click Properties to open the Database Properties dialog box.
3. In the Select a Page pane, click Options.
4. View the current recovery model in the Recovery Model list box, which should be set to Full.
5. Click the dropdown arrow in the Recovery Model section and select the Simple Recovery Model.
6. Click OK.
7. Right-click on the same database name and click Task-> Shrink-> Files. 
8. Use the File type drop-down menu and choose Log.
9. You can use the default setting of Release Unused Space or select Reorganize Pages before releasing unused space. You can specify the file size by supplying a value in the Shrink file to option. (Note: The shrink may take some time depending on the size of the file.)
10. After the shrink is completed, change the Recovery Model back to Full by clicking the Recovery Model dropdown arrow and selecting the Full Recovery Model.

After completing these steps, your database transaction log file should be at a normal size, and if you implement the process discussed in my “Avoiding ginormous transaction logs with SharePoint databases” blog post, you shouldn’t have to worry about ginormous transaction logs, or repeating the steps covered in this post again.