Search This Blog

Loading...

How to recover space from a huge transaction log file

Most of the novice dba’s forget to take the transaction log backups, which eventually ends up consuming a lot of disk space. This is one of the questions people ask me very often. Here are the steps which I tell them to follow.

  1. Take the full backup if the database
  2. Check to see if something is keeping SQL from reusing existing log space:
    SELECT name, log_reuse_wait_desc
    FROM sys.databases
    WHERE name = '<your_db_name>'
  3. Change the recovery to ‘Simple’
  4. Find the fileID, use the following statement USE <your_db_name>
    EXEC sp_helpfile
  5. Shrink the log file

USE <your_db_name>
DBCC SHRINKFILE ( 2 )

     6.  Resize the log file  so that it returns free space to the OS

ALTER DATABASE <your_db_name> MODIFY FILE ( NAME = <logical_log_file_name>, SIZE = 4GB )

     7.  Change the recovery to Full and take another backup