MSSQL Server - DBCC LOGINFO - Status 2. Log File FULL. Verify log_reuse_wait_desc.

Following is a scenario when LOG file is full. Following steps are taken to resolve the problem:
DBCC LOGINFO (Tuitionaffordable) -- Too big

-- This command gives you large data. The status 2 is uncommitted transactions.

BACKUP LOG Tuitionaffordable WITH TRUNCATE_ONLY -- Or NO_LOG

Msg 155, Level 15, State 1, Line 1
'truncate_only' is not a recognized BACKUP option.

-- This was good enough till 2005 but the command is depricated in 2008. Sql server forces you to take backup of t-logs to maintain integrity and consistency.

Try following command to get the 'Log Space (% used).
DBCC SQLPERF (logspace) - 100% Log Space Used for DB tuitionaffordable

People would go ahead and run CHECKPOINT. Problem is as follows when log is 100% full:

Msg 9002, Level 17, State 6, Line 1
The transaction log for database 'Tuitionaffordable' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases

Actually CHECKPOINT writes all dirty pages for the db to disk. These are data pages which are right now in the buffer cache and modified, but not yet written to the disk. The Checkpoints are created which save time during a later recovery by creating a point at which all dirty pages are surely written to disk.

I would advise to create a new log file with small size and run the CHECKPOINT again.

My DBA tried taking backup without giving any breathing space to DB and got following error:

Date  12/23/2010 8:39:34 AM
Log  Job History (AdHocBackup)
Step ID  1
Server  Tuition
Job Name  AdHocBackup
Step Name  AdhocBackup
Duration  00:00:01
Sql Severity  17
Sql Message ID  3013
Operator Emailed
Operator Net sent
Operator Paged
Retries Attempted  0

Custom Search

Message
Executed as user: SQL\Tuitionaffordable. The transaction log for database 'Tuitionaffordable' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases [SQLSTATE 42000] (Error 9002)  BACKUP DATABASE is terminating abnormally. [SQLSTATE 42000] (Error 3013).  The step failed.

Want to prepare in ten minutes - read article Interview and Beyond in 10 Min

Now you can try following command to take the backup of the log:

BACKUP LOG Tuitionaffordable TO DISK =  'E:\mssql\bak\Tuitionaffordable_log.trn'

You will surely get an error if the recovery model is simple otherwise your LOG will get truncated.
Msg 4208, Level 16, State 1, Line 1
The statement BACKUP LOG is not allowed while the recovery model is SIMPLE. Use BACKUP DATABASE or change the recovery model using ALTER DATABASE.
Msg 3013, Level 16, State 1, Line 1
BACKUP LOG is terminating abnormally.

If you stil face trouble, I would ask you to find out the open transaction. You will find some long open transaction which is not committed yet.

DBCC OPENTRAN

Transaction information for database 'Tuitionaffordable'.
Replicated Transaction Information:
        Oldest distributed LSN     : (0:0:0)
        Oldest non-distributed LSN : (983549:5658:1)
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Go ahead and find the related spid for this using following command.

select spid,open_tran from master..sysprocesses
where  open_tran <> 0

I would prefer to kill the spid only after confirming the text under this.
use http://tuitionaffordable.wordpress.com/sql-server-dba-interview-questions - Answer 7

Regards,
http://tuitionaffordable.webstarts.com