SQL Server - Transaction log chewing up harddrive space

Scope

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.

Steps

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.

Backup

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.

Truncate

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

Shrink

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

• Truncating the Transaction Log
• Shrinking the Transaction Log