Shrinking the Transaction Log in SQL Server

Problem

Well, this is a classical problem: The transaction log has been growing since who knows when and is now taking up almost all space on the server. You need to shrink the transaction log in order to free space on the server.

Solution

There are several solutions to this problem and it all depends on your database. This time I used the following script to get rid of all data in the transaction log and worth mentioning is that I didn’t care about the data in the transaction log; my goal was only to get rid of the unnecessary data and to reduce the size of the transaction log.

USE [MyDatabase]  
GO
ALTER DATABASE [MyDatabase]
SET RECOVERY SIMPLE;
GO
DBCC SHRINKFILE ([MyDatabase_Log], 1);
GO
ALTER DATABASE [MyDatabase]
SET RECOVERY FULL;
GO

The script above worked fine for me and you will find similar solutions on the internet. If you analyze the script you will see that it changes the recovery model from FULL to SIMPLE which means that the database will not longer log transactions that occur in the database. After the change we execute the DBCC SHRINKFILE command that will try to shrink the file stated by the logical filename (MyDatabase_Log) to the desired size in megabytes (the second parameter to the DBCC command). When the file has been shrunk we restore the recovery model to FULL that was the original setting for the database; of course this all depends on your scenario, but my guess if you read this is that you are using the FULL recovery model. 

While looking around I found another older solution that were used earlier to accomplish sort of the same thing, however it’s recommended not to use the approach mentioned below.

USE [MyDatabase]  
GO
DBCC SHRINKFILE([MyDatabase_Log], 1)
BACKUP LOG [MyDatabase] WITH TRUNCATE_ONLY
DBCC SHRINKFILE([MyDatabase_Log], 1)
GO 
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s