'The Transaction Log for database is full due to 'Active Transaction' - Unable to Backup, Enlarge, Truncate, or Shrink

3
votes

I have a SQL Server database that keeps giving me the following error:

Msg 9002, Level 17, State 4, Line 30
The transaction log for database 'DSC_DW_Summary' is full due to 'ACTIVE_TRANSACTION'.

I can see that the log file is at 100% of "Log Space Used" despite my using Simple Recovery and 1 minute recovery time. When I try do any of the following (suggested online as potential solutions) I get the error listed above.

  • Increase the MaxSize of the log file
  • Backup the database
  • Shrink the database

I also don't see any running transactions when I run:

SELECT * FROM sys.dm_tran_database_transactions

or:

DBCC OPENTRAN

Which gives me the result:

No active open transactions.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

So I'm baffled. Somehow I'm being prevented from resolving the issue because of an "Active Transaction" but I can't find any active transactions.

1
sql-serverdatabase-administration
OT - belongs on dba. But simple recovery model and "1 minute recovery time" are inconsistent. Simple means you can recover only to the last backup. Highly doubtful you are taking full backups once every minute. – SMor
What would you do differently @SMor? Set recovery time to weekly? – Jeremiah

1 Answers

2
votes

Start with checking what is preventing your log reuse:

SELECT [name], [log_reuse_wait_desc] FROM [sys].[databases];

Then also issue a CHECKPOINT to try and flush the log