February 21, 2020 at 3:26 pm
Hi All,
Here is the scenario on of the test servers. The transaction log is full. The database is in AG. Its SQL Server 2016 enterprise edition instance.
SQL Server Alert System: 'Error 9002 Log File Full' occurred on \\Server1
followed steps
=============
> The ldf file growth was limited to 100GB. There is lot of disk space available on drive though.
> DBCC SQLPERF(LOGSPACE); -- shows 100% usage
> Checked the recovery model and log reuse description from DMVs. it was Full and waiting ACTIVE transaction.
> dbcc loginfo --shows all VLFS are active i.e. status =2
> Taken consecutive 3 log backup and tried to check if there VLF status will change. nothing changed.
> I tried to increased the log file size using TSQL to 110GB. SQL still throws me an error message repeatedly
Msg 9002, Level 17, State 4, Line 3
The transaction log for database 'db1' is full due to 'ACTIVE_TRANSACTION'.
> I see multiple block chains as well.
Did anyone come across this situation? How to solve it without KILLing any sessions or restarting SQL Server ?
-Bob
February 21, 2020 at 3:34 pm
Not much you can do, I'm afraid. You either wait for the transaction to finish (in which case you may need to add more log space) or abort it (in which case it may take just as long or longer to roll back as it would have taken to finish). Make sure you identify the offending query and tune it so that the same thing doesn't happen next time it runs. What is the query doing - is it blocked by something else, waiting for user input, or just a complicated query?
John
February 21, 2020 at 3:49 pm
run a quick dbcc opentran against that database - then figure out what that spid is doing
it might not be as bad as john says. it could be a tiny transaction that is uncommitted and needs killing
have you issued a checkpoint command ?
this makes me shudder, (don't do this unless under adult supervision) - I've had to occasionally swap the DB to simple mode, checkpoint it, shrink the log (purely for if we have to restore it somewhere else) then put it back in full mode then kick off a full backup
MVDBA
February 21, 2020 at 5:06 pm
run a quick dbcc opentran against that database - then figure out what that spid is doing
it might not be as bad as john says. it could be a tiny transaction that is uncommitted and needs killing
have you issued a checkpoint command ?
this makes me shudder, (don't do this unless under adult supervision) - I've had to occasionally swap the DB to simple mode, checkpoint it, shrink the log (purely for if we have to restore it somewhere else) then put it back in full mode then kick off a full backup
To start a new log chain you can just do a differential backup instead of a full backup after putting the database back in the full recovery model.
February 21, 2020 at 7:26 pm
Because the database is part of an AG - make sure you check the send and redo queues. To see that - view the availability group dashboard by right-clicking on the availability group and selecting show dashboard. When that opens - add the send queue and redo queue.
If either of these are backed up - then the transaction log cannot be cleared even with a transaction log backup.
Also, verify the space on the secondaries for the transaction log has at least as much space available as the primary node. If you have run out of space on one of the secondaries - then the redo queue cannot complete its operation causing the primary to either fill the drive or reach the max size defined (it seems you have the growth limited on the log).
One final note - when a database is part of an AG then the status from DBCC LOGINFO will always be 2.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
February 24, 2020 at 6:58 am
Not much you can do, I'm afraid. You either wait for the transaction to finish (in which case you may need to add more log space) or abort it (in which case it may take just as long or longer to roll back as it would have taken to finish). Make sure you identify the offending query and tune it so that the same thing doesn't happen next time it runs. What is the query doing - is it blocked by something else, waiting for user input, or just a complicated query?
John
Hi John,
I endup killing that particular spid since there is no space left, it can't move further.
Even adding log space i.e. Alter database cmd failed since there is no space in the log file so had to kill that process and increase the log file.
February 24, 2020 at 9:48 am
this makes me shudder, (don't do this unless under adult supervision) - I've had to occasionally swap the DB to simple mode, checkpoint it, shrink the log (purely for if we have to restore it somewhere else) then put it back in full mode then kick off a full backup
If you do go down that route (and, believe me, it's a last resort sort of thing), then you'll have to take the DB out of the AG first...
Thomas Rushton
blog: https://thelonedba.wordpress.com
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply