patternsqlMinor
Database enters in recovery mode every time the Transaction Log is full
Viewed 0 times
thefulllogmodeeverytimedatabaserecoverytransactionenters
Problem
I am facing a situation that it is being somewhat hard to address. I need help to understand what is happening.
TL;DR: Every time the Transaction Log gets full in SQL Server it needs to shutdown the database to enter in Recovery Mode and rollback the offending transactions? Is this always done by design or this only happens when something bad happens?
The scenario:
One of our heavy used production databases, which runs several ETL jobs, and long running table batches, entered in Recovery Mode and became inaccessible for some time. This happened three times this week (this server is on for ~2 years, and we didn't notice this issue in the past).
Looking into the errors logs what happened was clear: the Transaction Log was full, the database needed to rollback the transaction, the rollback failed, the database shutdown, and started in recovery mode.
The DBA defends this as normal behavior of SQL Server. That is, according to him, every time the transaction log gets full and a transaction needs to rollback the database will enter in Recovery Mode due to the lack of log space. After the rollback (that can only be done in Recovery Mode according to him), the database will become available again.
I found no reference for this info. So I strongly disagree. I would really appreciate if someone convince me that I am wrong.
My point:
As far of my knowledge, a DBMS is built to manage/run queries. If it lacks space, the query will fail. Simple as it is. And I am not talking about performance of anything else, but availability only.
It makes no sense for me to accept that a DBMS needs by design to shutdown itself to rollback any transaction. In my understanding, it does not matter if I am running tons of queries or if the queries are bad designed. The bad queries should fail and life continues. Doesn't it?
My guess is that something else is making it fail, and I need to track what is happening.
Is my understanding wrong or this is really how SQL Server is
TL;DR: Every time the Transaction Log gets full in SQL Server it needs to shutdown the database to enter in Recovery Mode and rollback the offending transactions? Is this always done by design or this only happens when something bad happens?
The scenario:
One of our heavy used production databases, which runs several ETL jobs, and long running table batches, entered in Recovery Mode and became inaccessible for some time. This happened three times this week (this server is on for ~2 years, and we didn't notice this issue in the past).
Looking into the errors logs what happened was clear: the Transaction Log was full, the database needed to rollback the transaction, the rollback failed, the database shutdown, and started in recovery mode.
The DBA defends this as normal behavior of SQL Server. That is, according to him, every time the transaction log gets full and a transaction needs to rollback the database will enter in Recovery Mode due to the lack of log space. After the rollback (that can only be done in Recovery Mode according to him), the database will become available again.
I found no reference for this info. So I strongly disagree. I would really appreciate if someone convince me that I am wrong.
My point:
As far of my knowledge, a DBMS is built to manage/run queries. If it lacks space, the query will fail. Simple as it is. And I am not talking about performance of anything else, but availability only.
It makes no sense for me to accept that a DBMS needs by design to shutdown itself to rollback any transaction. In my understanding, it does not matter if I am running tons of queries or if the queries are bad designed. The bad queries should fail and life continues. Doesn't it?
My guess is that something else is making it fail, and I need to track what is happening.
Is my understanding wrong or this is really how SQL Server is
Solution
First of all few housekeeping rule.
Following two links can help you better manage transaction log file.
What you are experiencing is not normal behavior when transaction log file is full and cannot grow further.
When the transaction log becomes full, SQL Server Database Engine
issues a 9002 error. The log can fill when the database is online, or
in recovery. If the log fills while the database is online, the
database remains online but can only be read, not updated. If the log
fills during recovery, the Database Engine marks the database as
RESOURCE PENDING. In either case, user action is required to make log
space available.
The appropriate response to a full transaction log depends partly on what condition or conditions caused the log to fill. To discover what is preventing log truncation in a given case, use the log_reuse_wait and log_reuse_wait_desc columns of the sys.database catalog view.
What you are seeing is failure in transaction rollback. For more details read this post.
As per Paul Randal's blog post you hit a bug which was fixed in SQL 2012 SP4.
More details on error 3314:
Reference:
- You (or your DBA) should manage transaction log space depending on your recovery model.
- Do not let transaction log to get full and impact your database/application availability.
Following two links can help you better manage transaction log file.
- SQL Server Transaction Log Architecture and Management Guide
- How do you clear the SQL Server transaction log? Answered by Aaron Bertrand which also listed some great resources around transaction log issues.
What you are experiencing is not normal behavior when transaction log file is full and cannot grow further.
When the transaction log becomes full, SQL Server Database Engine
issues a 9002 error. The log can fill when the database is online, or
in recovery. If the log fills while the database is online, the
database remains online but can only be read, not updated. If the log
fills during recovery, the Database Engine marks the database as
RESOURCE PENDING. In either case, user action is required to make log
space available.
The appropriate response to a full transaction log depends partly on what condition or conditions caused the log to fill. To discover what is preventing log truncation in a given case, use the log_reuse_wait and log_reuse_wait_desc columns of the sys.database catalog view.
What you are seeing is failure in transaction rollback. For more details read this post.
- INF:What happens to a database when transaction rollback fails?
As per Paul Randal's blog post you hit a bug which was fixed in SQL 2012 SP4.
- 2012/2014 bug that can cause database or server to go offline
More details on error 3314:
- Troubleshooting Error 3313, 3314, 3414, or 3456 (SQL Server)
Reference:
- Troubleshoot a Full Transaction Log (SQL Server Error 9002)
Context
StackExchange Database Administrators Q#204479, answer score: 3
Revisions (0)
No revisions yet.