The transaction log for database is full due to 'ACTIVE_TRANSACTION'
Question
Saturday, January 25, 2014 1:24 PM
Hello team,
One of the my database was in Recovery Pending state and we are trying to restore that database again with latest full backup but I am unable to do so I am getting below error.
The transaction log for database <db Name> is full due to 'ACTIVE_TRANSACTION'.
Now database is in Recovery pending state and when I try to restore it goes to 99% and and getting failed.
Even I tried to bring db online by
ALTER DATABASE MyDB SET ONLINE but no luck. I have also put my database in emergency mode
The log cannot be rebuilt because there were open transactions/users when the database was shutdown, no checkpoint occurred to the database, or the database was read-only. This error could occur if the transaction log file was manually deleted or lost due to a hardware or environment failure.
Msg 1813, Level 16, State 2, Line 1
My suspicion is that the max growth size was reached and it can’t grow to complete the active transaction.
Rahul
All replies (4)
Saturday, January 25, 2014 5:36 PM ✅Answered
That would indicate that the backup is in a bad shape, unforunately. Do you have an earlier backup you can try with?
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
Saturday, January 25, 2014 3:22 PM
What happens if you try to restore the backup to a database with a different name?
BACKUP DATABASE dbNameCopy FROM DISK = '<path>'
MOVE 'dbName' TO '<newpath.mdf>',
MOGE 'dbName_log' TO '<newpath.ldf>'
The names after MOVE are the logical names for the file. You can retreieve these name with RESTORE HEADERONLY.
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
Saturday, January 25, 2014 3:33 PM
Thanks for reply. I did that too. I have restored that backup with new db name and it also went to same state. Even I restored backup with move command and log file got moved to new location but state of db is same.(Recovery pending). I do not have any issue with disk space. I have space but log file size can not grow as its restricted.
Rahul
Monday, January 27, 2014 9:13 AM
Hi,
Have you checked DBCC SQLPERF(log space)?
How much free space on your drive?
You can use the sp_add_log_file_recover_suspect_db stored procedure in this situation. After the file is added, sp_add_log_file_recover_suspect_db turns off the suspect setting and completes the recovery of the database. The parameters are the same as those for ALTER DATABASE database_name ADD LOG FILE.
Also, I recommend to modify the log file automatic growth settings, set the log file to unlimited growth.
Thanks.
Tracy Cai
TechNet Community Support