I’m sure by now most of you have encountered SharePoint databases that have a SQL Server database data file (.mdf) at a size that is as little as several hundred MB’s, yet the associated log file (.ldf) is ginormous and may be GB’s in size. This ginormous transaction log file will continue to grow until the drive containing the transaction log (t-log) runs out of disk space. At this point there is no room for transactions to be added to the t-log, so users cannot perform insert, update, or delete actions in SharePoint. This restriction is applied because every type of SQL data modification is first written to the transaction log and then written to the data file, but at this point there is no room on the drive to write to the transaction log.
I’ll have another blog article (Avoiding Ginormous Transaction Logs) which discusses how to prevent the transaction log from getting too large, but what if you discover the ginormousity (yes I made up this word) of the log and need to shrink it. You can use the following steps to shrink the t-log file to a reasonable size and also use the steps in my other blog article to prevent this from happening again.
You’ll begin by logging into the server hosting the SQL Server SharePoint databases, and open SQL Server Management Studio (SSMS). Be sure you are connected to the instance of SQL Server that contains the SharePoint content.
Locate the database that has the ginormous t-log file and use either of the following sets of steps to shrink the ginormous transaction log file down to a reasonable size.
Using the ALTER DATABASE statement to shrink the transaction log file
Open the SQL Server Management Studio and click Query Analyzer to open the Query Analyzer window. Type in the following commands sequentially and execute them individually:
USE MTP_RC_DB; — Connect to the database that you want to shrink
ALTER DATABASE MTP_RC_DB SET RECOVERY SIMPLE; — Set the recovery model to SIMPLE
CHECKPOINT; — Issue a CHECKPOINT to purge out the inactive transactions
DBCC SHRINKFILE (MTP_RC_DB_LOG, 5); — Shrink the transaction log file to a reasonable size
ALTER DATABASE MTP_RC_DB SET RECOVERY FULL; — Set the recovery model back to FULL
Using the SSMS GUI to shrink the transaction log file
Open the SQL Server Management Studio.
- Expand the Databases node and expand User Databases
- Right-click the database, and click Properties, which opens the Database Properties dialog box.
- In the Select a page pane, click Options.
- View the current recovery model in the Recovery model list box, which should be set to Full
- Click the dropdown arrow in the recovery model section and select the Simple recovery model
- Click OK.
- Right-click on the same database name and click Task-> Shrink-> Files
- Use the File type drop-down menu and choose Log
- You can use the default setting of Release Unused Space or select Reorganize pages before releasing unused space, and 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 how large the file is and how much it has to shrink.
- After the shrink completes, 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 transaction log should be at a normal size and if you implement the steps discussed in my Avoiding Ginormous Transaction Log files blog article you shouldn’t have to repeat the steps in this blog again.