Pages - Menu

SQL Server - Transaction log chewing up harddrive space


In a production server that have Recovery Model set to Full mode, it is easily noticeable the transaction log will grow to some unmanageable size in a short period of time depending on the number of activities of the database.


There are numerous ways to do it, and depends on different version of SQL Server. I found this is the easiest and worked across the board.


Do a full backup before we begin. This will create a last well known tlog backup as we are about to destroy the chain.

Ideally you want to switch your website off during this operations. Otherwise there will be no logging during this operation time. I personally think that's not a big deal, but I thought I will point it out anyway.


Change Recovery Model to Simple Mode. This is to truncate the transaction log. 


Run command to shrink the log.

exec sp_helpdb database_name

DBCC SHRINKFILE (transaction_log_name, 1)

exec sp_helpdb database_name

Change Recovery Model back to what it was - Full Mode

Backup Again

At this point, I prefer to do another full backup. It is not compulsory, but will clearly define where the new tlog chain begin.

Further Reading

No comments:

Post a comment