SQL Server sys.databases log_reuse_wait question

Question

I was investigating the rapid growth of a SQL Server 2005 transaction log when I found that transaction logs will only truncate correctly - if the sys.databases "log_reuse_wait" column is set to 0 - meaning that nothing is keeping the transaction log from reusing existing space.

One day when I was intending to backup/truncate a log file, I found that this column had a 4, or ACTIVE_TRANSACTION going on in the tempdb. I then checked for any open transactions using DBCC OPENTRAN('tempdb'), and the open_tran column from sysprocesses. The result was that I could find no active transactions anywhere in the system.

Are the settings in the log_reuse_wait column accurate? Are there transactions going on that are not detectable using the methods I described above? Am I just missing something obvious?

Nanine

Answer 1

I still don't know why I was seeing the ACTIVE_TRANSACTION in the sys.databases log_reuse_wait_desc column - when there were no transactions running, but my subsequent experience indicates that the log_reuse_wait column for the tempdb changes for reasons that are not very clear, and for my purposes, not very relevant. Also, I found that running DBCC OPENTRAN, or the "select open_tran from sysprocess" code, is a lot less informative than using the below statements when looking for transaction information:

select * from sys.dm_tran_active_transactions

select * from sys.dm_tran_session_transactions 

select * from sys.dm_tran_locks

Nanine

Answer 2

Here there are explanations how log_reuse_wait_desc is working:

We also need to understand how the log_reuse_wait_desc reporting mechanism works. It gives the reason why log truncation couldn’t happen the last time log truncation was attempted. This can be confusing – for instance if you see ACTIVE_BACKUP_OR_RESTORE and you know there isn’t a backup or restore operation running, this just means that there was one running the last time log truncation was attempted.

So in your case there is no ACTIVE TRANSACTION right now, but it was when log truncation was attempted last time.

Weinrich

Answer 3

There are a couple of links to additional tools/references you can use to help troubleshoot this problem on the References link for this video:
Managing SQL Server 2005 and 2008 Log Files

That said, the information in log_reuse_wait should be accurate. You likely just had a stalled or orphaned transaction that you weren't somehow able to spot.

Beebeebe

Thanks for the link! I watched the video and learned some things, but unfortunately it did not address my core question. – Nanine

Answer 4

My answer from The Log File for Database is Full:

As soon as you take a full backup of the database, and the database is not using the Simple recovery model, SQL Server keeps a complete record of all transactions ever performed on the database. It does this so that in the event of a catastrophic failure where you lose the data file, you can restore to the point of failure by backing up the log and, once you have restored an old data backup, restore the log to replay the lost transactions.

To prevent this building up, you must back up the transaction log. Or, you can break the chain at the current point using the TRUNCATE_ONLY or NO_LOG options of BACKUP LOG.

If you don't need this feature, set the recovery model to Simple.

Nebula

I was aware of the information in your answer. I fixed the growing transaction log by setting the recorvery model to simple. My question however, is why log_reuse_wait should show me that there are active transactions, when I cannot find any in the system. – Nanine

Answer 5

The data is probably accurate. What you need to do is have a regular transaction log backup. Contrary to other advice you should NOT use the NO_TRUNCATE option on 2005 as it clears the log of transactions committed but it doesn't back them up.

What you should be doing is performing a tail-log backup by using the BACKUP LOG statement with NO_TRUNCATE option. You should be applying regular transaction logs throughout the day as well. This should help keep the size fairly manageable.

Stopover

Answer 6

I just had this same problem where the database was showing ACTIVE_TRANSACTION for log_reuse_wait_desc and yet looking in sys.dm_tran_active_transactions showed now transactions, neither did sp_whoisactive or DBCC OPENTRAN(). The database had 300MB left of space and the drive was full from the log growing to max size (couldn't grow anymore).

This was a dev databases and I saw a lot of posts around the internet (like this one) but no real fixes so I decided to try some things, all of which did not fix the problem

1. Set the recovery model to Simple
2. Took the database offline then brought on again
3. Took the entire SQL Service offline and then turned back on again
4. Set the database to RESTRICTED_USER mode, stopped SQL Agent and killed all transactions

Even after all that it still said ACTIVE_TRANSACTION. In the end I did a

USE [InsEx]
GO
DBCC SHRINKFILE (N'DEV_log' , 0, TRUNCATEONLY)
GO

(This is the same as doing the GUI Shrink --> "Release unused space"). That fixed it. Suddenly the log was 99% free and the log_resuse_wait_desc was NOTHING.

This seems to reflect what @agdk26 said that the value of log_resuse_wait_desc was when the last log truncate occurred but it doesn't really explain to me why setting the recovery model to Simple did not truncate the log (as I have seen it do many times in the past).

Waterlogged

Answer 7

Hm, tricky. Could it be that the question it self to sys.databases is causing the ACTIVE_TRANSACTION? In that case though, it should be in the MASTER and not the TEMPDB.

Nissie

Possibly, but after an hour or so the ACTIVE_TRANSACTION went away and went back 0 (NOTHING). If your suggestion were true, then every time I queried sys.databases the tempdb (or master) would always be in an ACTIVE_TRANSACTION mode. – Nanine