What does it mean when DBCC LOGINFO returns many rows with status = 2 and DBCC OPENTRAN returns no active open transactions? 

It can mean a lot of things, for example, you have full recovery but haven't backed up the transaction log, you have CDC enabled on the database, or your are using replication, or you are using database mirroring.  What problem specifically are you having?  If you want to know why the log is not freeing for reuse, look at the log_reuse_wait_desc column for the database in sys.databases.

I just want to understand why the status = 2 when there is no active transactions.   The log_reuse_wait_desc column has the following values: ACTIVE_TRANSACTION, NOTHING, CHECKPOINT, and LOG_BACKUP.  

If I run dbcc loginfo on database having the value ACTIVE_TRANSACTION, I expect 1 or more rows with status = 2.

If I run dbcc loginfo on database having the value NOTHING, I expect 1 row with status = 2 and that row has the highest FSeqNo.

If I run dbcc loginfo on database having the value CHECKPOINT, I expect 1 or more rows with status = 2.  If there is no active transaction, I expect that after a checkpoint is issued explicitly or automatically, active logs (status = 2) will become inactive (status = 0).   I have tried issuing a checkpoint for such database but the status does not change and this is the part that I don't understand.   (Update: I guess the reason that the status is not changed because those "inactive" logs are still needed in order to support FULL or BULK LOGGED recovery.)

If I run dbcc loginfo on database having the value LOG_BACKUP, I expect 1 or more rows with status = 2.  If there is no active transaction, I expect that after issuing backup log, active logs (status = 2) will become inactive (status = 0) and then all inactive logs will be truncated.

 

 

 

 

• Edited by PCSQL66 Friday, June 17, 2011 12:21 AM Update

Hi,

Status = 2 means that VLF can't be reused (overwritten) at this time and it doesn't necessarily mean that VLF is still active and writing transactions to that VLF. As Jonathan already mentioned, it means that the VLF is waiting for backup/REPL/Mirroring etc...

http://msdn.microsoft.com/en-us/library/ms189573.aspx

CHECKPOINT only truncates the transaction log (marks the VLF for reuse) only in simple recovery model. In Full recovery, you have to take log backup.

http://technet.microsoft.com/en-us/magazine/2009.02.logging.aspx

HTH

The log_reuse_wait_desc column has a total of 8 values that can reflect why the log is not being reused as shown in the following BOL topic. (http://msdn.microsoft.com/en-us/library/ms345414.aspx)  Unfortunately without a repro that shows what you are asking about I can't offer more than what I have, I have never paid attention to exactly how many VLF's are in use in my system at any given point in time.  If I have a problem with log growth, I focus on the cause of the log growth and work on resolving whatever that problem is.  If you have a way to repro what you are asking about where the log doesn't truncate properly post it here, or if it is to big, feel free to email it to me and if I can't explain it, I'll ask Paul Randal to look at it and have him explain it.

I just want to understand why the status = 2 when there is no active transactions.   The log_reuse_wait_desc column has the following values: ACTIVE_TRANSACTION, NOTHING, CHECKPOINT, and LOG_BACKUP.  

If I run dbcc loginfo on database having the value ACTIVE_TRANSACTION, I expect 1 or more rows with status = 2.

If I run dbcc loginfo on database having the value NOTHING, I expect 1 row with status = 2 and that row has the highest FSeqNo.

If I run dbcc loginfo on database having the value CHECKPOINT, I expect 1 or more rows with status = 2.  If there is no active transaction, I expect that after a checkpoint is issued explicitly or automatically, active logs (status = 2) will become inactive (status = 0).   I have tried issuing a checkpoint for such database but the status does not change and this is the part that I don't understand.   (Update: I guess the reason that the status is not changed because those "inactive" logs are still needed in order to support FULL or BULK LOGGED recovery.)

If I run dbcc loginfo on database having the value LOG_BACKUP, I expect 1 or more rows with status = 2.  If there is no active transaction, I expect that after issuing backup log, active logs (status = 2) will become inactive (status = 0) and then all inactive logs will be truncated.

You will always have at least one active VLF - there will never be an occasion where all VLF's will have a status = 2.  Once that VLF is full and the next one is opened, then either a CHECKPOINT or  LOG BACKUP will set the status = 0 (if there are no open transactions that were started in that VLF and no transactions waiting to be shipped, replicated or mirrored).

 

You will always have at least one active VLF - there will never be an occasion where all VLF's will have a status = 2.  Once that VLF is full and the next one is opened, then either a CHECKPOINT or  LOG BACKUP will set the status = 0 (if there are no open transactions that were started in that VLF and no transactions waiting to be shipped, replicated or mirrored).

Hi Jeff,

Not trying to be smart here but the blanket statement that all VLF's will never have status = 2 is NOT incorrect. Here is a simple repro.

use master

go

drop database db1

go

use master

go

create database db1

go

backup database db1 to disk = 'c:\Temp\db1.bak'

go

use db1

go

create table NewTable (c1 bigint, c2 char(8000))

go

DBCC LOGINFO(db1)

begin tran

insert NewTable (c1, c2)

     select top (30) object_id, convert(char(8000), name) from sys.columns

 

DBCC LOGINFO(db1)

rollback tran

 

Just to add my .02 cents:

I believe there can be things not caught by DBCC LOGINFO. I believe db mirroring is/was one of those things, but there can be other circumstances as well.

I'm in the process of backing up inactive databases and deleting them from SQL Servers.   Since those are inactive databases, I just want to do a full database backup.   However, I notice some of the log files are quite large.   So, I tried to shrink them but they could not be shrinked since some of the VLFs had status = 2 even though I issued checkpoint manually.  Now, I understand that VLF has 4 states not just simply inactive and active.

I'm doing the following to resolve my issue:

switch the database to simple recovery

issue checkpoint

shrink the log file

switch the database back to full recovery

do a full database backup

Is this the recommended way to handle this?

 

Thanks for all the helps.

• Edited by PCSQL66 Friday, June 17, 2011 5:28 PM clear spaces

If all you do is a FULL backup, the log is not cleared for reuse, only a LOG backup will free the VLF's for reuse under FULL recovery.  CHECKPOINT doesn't matter and neither does the FULL backup, you have to do a LOG backup for the VLF's to truncate for reuse, or do like you did and put the database into SIMPLE recovery which truncates the log completely.  Why are you shrinking the transaction log file to do a FULL backup exactly?  The backup size doesn't include unused space in the log or data files, so there is no need to truncate/shrink any of the files to perform a FULL backup.

Sankar - you are right, I meant to say there will never be an occasion where all VLF's will have a status = 0.

In other words, there will always be at least one active VLF (status = 2).

 

OF NOTE,

The Full Backup file contains the file size of the Log file even if empty. When restoring to a different server the instance will try and reserve the continuous space for performance and may fail if enough space is not available. It is best to execute log backups every 15 minutes or less to improve disk performance and healthy background tasks regardless of your data recovery needs. This "Right Sizes" your log files based on implementation.