How to recover space from a huge transaction log file

Post a Comment

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
  5. USE <your_db_name>
    EXEC sp_helpfile
  6. Shrink the log file

USE <your_db_name>

     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

Related Posts

There is no other posts in this category.

Post a Comment

Subscribe Our Newsletter