Troubleshoot a Full Transaction Log

The appropriate response to a full transaction log depends partly on what condition or conditions caused the log to fill. To discover what is preventing log truncation in a given case, use the log_reuse_wait and log_reuse_wait_desc columns of the sys.database catalog view. For more information, see sys.databases (Transact-SQL). For descriptions of factors that can delay log truncation, see The Transaction Log (SQL Server).

Important note Important

If the database was in recovery when the 9002 error occurred, after resolving the problem, recover the database by using ALTER DATABASE database_name SET ONLINE.

Alternatives for responding to a full transaction log include:

• ·         Backing up the log.
• ·         Freeing disk space so that the log can automatically grow.
• ·         Moving the log file to a disk drive with sufficient space.
• ·         Increasing the size of a log file.
• ·         Changing database recovery model.
• ·         Adding a log file on a different disk.
• ·         Completing or killing a long-running transaction.

These alternatives are discussed in the following sections. Choose a response that fits your situation best.

For more information please visit http://msdn.microsoft.com/en-IN/library/ms175495.aspx