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.
- Take the full backup if the database
- 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>' - Change the recovery to ‘Simple’
- Find the fileID, use the following statement USE <your_db_name>
EXEC sp_helpfile - 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
