CODEWITHSUNDEEP
sql-serverdatabase-administration
Jeremiah
Jeremiah

Reputation: 43

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

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.

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.

Upvotes: 3

Views: 28215

Answers (1)

Martin Cairney
Martin Cairney

Reputation: 1767

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

Upvotes: 3

Related Questions