Arman Gungor's Blog Litigation Support and Technology

7Sep/100

How to Shrink SQL Database Logs

SQL database logs have the tendency to get very large if you are using SQL in full recovery mode. If you have come to a point where you are certain that you will not need point in time recovery and if you would like to reclaim the space log files are taking, you can issue the following command in SQL 2005 or before. This will truncate the transaction log and shrink the log file. For the purposes of this example, let's assume that our log file is named "Sample_log.ldf" and our database is called Sample.

DBCC SHRINKFILE(Sample_log, 1)
BACKUP LOG Sample WITH TRUNCATE_ONLY
DBCC SHRINKFILE(Sample_log, 1)

If you are using SQL 2008, you will notice that the TRUNCATE_ONLY backup option has been discontinued. The only way I was able to find to truncate the log in SQL 2008 was to temporarily switch the database recovery model to simple, shrink the log and switch back to full recovery mode. You can accomplish this as follows:

ALTER DATABASE Sample SET RECOVERY SIMPLE
CHECKPOINT
DBCC SHRINKFILE(Sample_log, 1)
ALTER DATABASE Sample SET RECOVERY FULL

From a best practices standpoint, truncating log files is not recommended.  If you need full recovery, you should ideally invest into sufficient disk space to accommodate the log files.

Bookmark and Share
Comments (0) Trackbacks (0)

No comments yet.


Leave a comment


No trackbacks yet.