gotchasqlCritical
Why Does the Transaction Log Keep Growing or Run Out of Space?
Viewed 0 times
whythespacelogkeepgrowingtransactiondoesoutrun
Problem
This one seems to be a common question in most forums and all over the web, it is asked here in many formats that typically sound like this:
In SQL Server -
my transaction log file to a healthy size?
In SQL Server -
- What are some reasons the transaction log grows so large?
- Why is my log file so big?
- What are some ways to prevent this problem from occurring?
- What do I do when I get myself on track with the underlying cause and want to put
my transaction log file to a healthy size?
Solution
A Shorter Answer
You probably either have a long running transaction running (Index maintenance? Big batch delete or update?) or you are in the "default" (more below on what is meant by default) recovery model of full and have not taken a log backup (or aren't taking them frequently enough).
If it is a recovery model issue, the simple answer could be to switch to the simple recovery model if you do not need point in time recovery and regular log backups. Many people, though, make that their answer without understanding recovery models. Read on to understand why it matters and then decide what you do. You could also just start taking log backups and stay with the full recovery model.
There could be other reasons, but these are the most common. This answer begins to dive into the most common two reasons and gives you some background information on the why and how behind the reasons as well as explores some other reasons.
A Longer Answer
What scenarios can cause the log to keep growing? There are many reasons, but usually the reason is one of the following two patterns: There is a misunderstanding about recovery models or there are long running transactions. Read on for details.
Top reason 1/2: Not Understanding Recovery Models
(In the full recovery model and not taking log backups - This is the most common reason)
While this answer is not a deep dive into SQL Server recovery models, the topic of recovery models is critical to this problem.
In SQL Server, there are three recovery models:
We'll ignore bulk-logged for now. We'll sort of say it is a hybrid model and most people who are in this model are there for a reason and understand recovery models.
The two we care about, and their confusion, are the cause of the majority of the cases of people having this issue are simple and full.
Intermission: Recovery in General
Before we talk about recovery models, let's talk about recovery in general. If you want to go even deeper with this topic, just read Paul Randal's blog and as many posts on it as you want. For this question, though:
-
Crash/Restart Recovery
One purpose of the transaction log file is for crash/restart recovery. For the rolling forward and rolling back of work that was either done (rolling forward/redo) before a crash or restart and the work that was started but not finished after a crash or restart (rolling back/undo).
It is the job of the transaction log to see that a transaction started but never finished (rolled back or crash/restart happened before the transaction committed). In that situation, it is the log's job to say "Hey.. this never really finished, let's roll it back" during recovery. It is also the log's job to see that you did finish something and that your client application was told it was finished (even if it hadn't yet hardened to your data file) and say "Hey.. this really happened, let's roll it forward, let's make it like the applications think it was" after a restart. Now there is more but that is the main purpose.
-
Point in Time Recovery
The other purpose for a transaction log file is to give us the ability to recover to a point in time due to an "oops" in a database or to guarantee a recovery point in the event of a hardware failure involving the data and/or log files of a database.
If this transaction log contains the records of transactions that have been started and finished for recovery, SQL Server can and does then use this information to get a database to where it was before an issue happened. But that isn't always an available option for us. For that to work we have to have our database in the right recovery model, and we have to take log backups.
Recovery Models
Onto the recovery models:
-
Simple recovery model
With the above introduction, it is easiest to talk about the simple recovery model first. In this model, you are telling SQL Server: "I am fine with you using your transaction log file for crash and restart recovery..." (You really have no choice there. Look up ACID properties and that should make sense quickly.) "...but once you no longer need it for that crash/restart recovery purpose, go ahead and reuse the log file."
SQL Server listens to this request in simple recovery and only keeps the information it needs to do crash/restart recovery. Once SQL Server is sure it can recover because data is hardened to the data file (more or less), the data that has been hardened is no longer necessary in the log and is marked for truncation - which means it gets re-used.
-
Full recovery model
With full recovery, you are telling SQL Server that you want to be able to recover to a specific point in time, as long as your log file is available or to a specific point in time that is covered by a log backup.
In this case, when SQL Server reaches the point where it would be safe to truncate the log file under the simple recovery model, it will not do that. Instead, it lets the log file continue to grow and will allow
You probably either have a long running transaction running (Index maintenance? Big batch delete or update?) or you are in the "default" (more below on what is meant by default) recovery model of full and have not taken a log backup (or aren't taking them frequently enough).
If it is a recovery model issue, the simple answer could be to switch to the simple recovery model if you do not need point in time recovery and regular log backups. Many people, though, make that their answer without understanding recovery models. Read on to understand why it matters and then decide what you do. You could also just start taking log backups and stay with the full recovery model.
There could be other reasons, but these are the most common. This answer begins to dive into the most common two reasons and gives you some background information on the why and how behind the reasons as well as explores some other reasons.
A Longer Answer
What scenarios can cause the log to keep growing? There are many reasons, but usually the reason is one of the following two patterns: There is a misunderstanding about recovery models or there are long running transactions. Read on for details.
Top reason 1/2: Not Understanding Recovery Models
(In the full recovery model and not taking log backups - This is the most common reason)
While this answer is not a deep dive into SQL Server recovery models, the topic of recovery models is critical to this problem.
In SQL Server, there are three recovery models:
- Full
- Bulk-Logged
- Simple
We'll ignore bulk-logged for now. We'll sort of say it is a hybrid model and most people who are in this model are there for a reason and understand recovery models.
The two we care about, and their confusion, are the cause of the majority of the cases of people having this issue are simple and full.
Intermission: Recovery in General
Before we talk about recovery models, let's talk about recovery in general. If you want to go even deeper with this topic, just read Paul Randal's blog and as many posts on it as you want. For this question, though:
-
Crash/Restart Recovery
One purpose of the transaction log file is for crash/restart recovery. For the rolling forward and rolling back of work that was either done (rolling forward/redo) before a crash or restart and the work that was started but not finished after a crash or restart (rolling back/undo).
It is the job of the transaction log to see that a transaction started but never finished (rolled back or crash/restart happened before the transaction committed). In that situation, it is the log's job to say "Hey.. this never really finished, let's roll it back" during recovery. It is also the log's job to see that you did finish something and that your client application was told it was finished (even if it hadn't yet hardened to your data file) and say "Hey.. this really happened, let's roll it forward, let's make it like the applications think it was" after a restart. Now there is more but that is the main purpose.
-
Point in Time Recovery
The other purpose for a transaction log file is to give us the ability to recover to a point in time due to an "oops" in a database or to guarantee a recovery point in the event of a hardware failure involving the data and/or log files of a database.
If this transaction log contains the records of transactions that have been started and finished for recovery, SQL Server can and does then use this information to get a database to where it was before an issue happened. But that isn't always an available option for us. For that to work we have to have our database in the right recovery model, and we have to take log backups.
Recovery Models
Onto the recovery models:
-
Simple recovery model
With the above introduction, it is easiest to talk about the simple recovery model first. In this model, you are telling SQL Server: "I am fine with you using your transaction log file for crash and restart recovery..." (You really have no choice there. Look up ACID properties and that should make sense quickly.) "...but once you no longer need it for that crash/restart recovery purpose, go ahead and reuse the log file."
SQL Server listens to this request in simple recovery and only keeps the information it needs to do crash/restart recovery. Once SQL Server is sure it can recover because data is hardened to the data file (more or less), the data that has been hardened is no longer necessary in the log and is marked for truncation - which means it gets re-used.
-
Full recovery model
With full recovery, you are telling SQL Server that you want to be able to recover to a specific point in time, as long as your log file is available or to a specific point in time that is covered by a log backup.
In this case, when SQL Server reaches the point where it would be safe to truncate the log file under the simple recovery model, it will not do that. Instead, it lets the log file continue to grow and will allow
Context
StackExchange Database Administrators Q#29829, answer score: 365
Revisions (0)
No revisions yet.