Log Shipping – NORECOVERY vs. STANDBY mode

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