SQL Server DBA : Reduce the size of a large database log file

I recently came across a situation where one of the drives in production server is almost full.

There are two large databases and they were backed up daily once. No other backup plans were in place. That drive is filled by the log files of these two databases (~200GB each).

This has happened because the databases were in full recovery mode but transaction logs are not backed up.

Here are the steps to solve this issue:

  1. Take a full backup of the databases (Not required but as a precaution)
  2. Set the recovery model to simple
  3. Shrink the log file
  4. Set the recovery model back to full
  5. Take a full backup
  6. Setup a job to backup transaction log frequently

Note: Shrinking the log file is not a good option to do it regularly. I had to use the shrinking option because this is one-off and the log file has grown that big is due to lack of log backups.

Here is the script to perform the above tasks:

-- Full Backup
backup database TestDB to  disk = N'H:\Backup\TestDB_20150922_1308.bak'
with name = N'TestDB-Full Database Backup', compression,  stats = 10
go
alter database TestDB
set recovery simple;
go
-- See the name of the log file
sp_helpfile
go
-- shrink the truncated log file to 100 mb.
dbcc shrinkfile (TestDB_log, 100);
go
-- reset the database recovery model.
alter database TestDB
set recovery full;
go