SharePoint: How to shrink transaction log file
While restoring a production database in my local environment, first of all I shrink the database transaction log file, because it usually takes up a lot of space. I use SQL Server 2008, and in order to get the transaction log shrunk, I can suggest swithing the database recovery model from FULL to SIMPLE and back. To accomplish that:
- Switch the database recovery model to SIMPLE using the following command:
- ALTER DATABASE <Your Database Name> SET RECOVERY SIMPLE;
- Shrink the database or transaction log file using Microsoft SQL Server Management Studio:
Or use the following command to shrink the transaction log file, for example, to 5 MB:- DBCC SHRINKFILE (<Your Database Name>_Log, 5);
- Switch the database recovery model back to FULL using the following command:
- ALTER DATABASE <Your Database Name> SET RECOVERY FULL;
If it’s not a production database, you can actually skip step 3, leaving the database recovery model in SIMPLE state; the Transaction log in this case is not going to grow. But keep in mind that some people might complain that it causes problems when attempting to delete SiteCollection from SharePoint Central Administration. I’ve never faced such issues, but you may want to keep FULL recovery model just in case.