How to recover from the Transaction Log (t-log) filling up!!!
This is the most frequently asked question in the forums or blogged. Hence, I thought let me write something on this so that this would be helpful to the DBA while they face this issue.
What is Transaction Log (T-Log)?
Why T-Log is full?
Why my T-Log got filled up so fast?
How do I get rid of T-Log filling up so quickly?
What’s the issue all about?
The problem here lies in the statement itself, that’s T-Log is getting filled up or is full!!!!
To get better understanding of the problem statement let me give you some insight into T-Log and significance of it.
As the name says, it logs all the transactions that are executed in SQL Server, be it fully logged or minimal logged (based on the recovery model you’ve chosen). This is what the simplest definition I can give you.
What does T-Log do and why it is so critical?
As I said, it logs all the transactions that are executed in SQL Server. Now you would be thinking that why it is needed!! Let me explain you: when ever and what ever the transaction happened, you always wanted to ensure that the data you entered are in consistent state and whenever you want to see them they are available to you, the utmost requirement.
What role T-Log plays here is, while recording all these transactions it ensures that they are committed so as it will be available and will remain consistent. In the time of failure or non-availability of the database server or severe damaged at the physical failure of server; backup of the T-Log along with the full database backup (off course it is required) would really helps you to recover your database to the point-in-time (I’ll explain you this in my next article). When ever you restart your SQL Server, observer your SQL Server logs carefully you can see some thing like below:
SQL Server is recovering individual transactions, and all incomplete transactions to the database.
This message means, that T-Log writing of all the commited transactions and roll forward all the uncommited transactions.
What else T-Log can do?
T-Log is help full to recover database point-in-time in case of failure, it is also useful to setup High Availability for Server(I’ll explain more in my upcoming articles). That means you can setup Log Shipping and database mirroring with the help of T-Logs.
Now as we know what T-Log is and what is significance of it, let’s come back to the main topic,
Why T-Log is getting full?
The cause which leads to T-Log to fill up, I am penning down some points I’ve recollected, they are:
1. Transactions are not committed
2. Transaction is not open but is active for long time
3. While creating / modifying Index
4. Bulk Operations
5. Auto Growth option of database files
6. Group by and Order by clause will fill up tempdb
7. Un-replicated transactions in T-Logs
8. T-Log backups are not taken for long
How to get rid off this messages (Solutions):
1. Ensure that application is written appropriately, and each transaction is wrapped between begin and commit transaction. This will not only helps to keep your data in consistent form but helps you to reduce the blockings.
2. This are the transactions that are not open but active for long time, means while user is sending some input to the database server his/her system got hung, rebooted or shutdown and network doesn’t send this information appropriately to the database thus, SQL Server understands this transaction is open but not active. This kind of sessions can be killed provided you’ve examine what this process is doing exactly by using DBCC Inputbuffer (SPID), be cautious while killing any process as it would take 4 times longer. * To know more on how Kill command works click here… especially when it rollback transaction
3. This is the most important point; creating or recreating an index would occupy much of the space as it is fully logged operation. When you run DBCC DBREINDEX it will drop existing index and recreate index (will recreate every index of your table if you won’t give name of index) . I would advise that you care fully examine output of DBCC SHOWCONTIG or sys.dm_db_index_physical_stats and based on that fact you can go ahead and defrag your index. Recreate your index only when required.
4. Although, bulk operations are minimally logged but it occupies the space certainly, in case your SQL Server is in full recovery mode (which is highly recommended for point-in-time recovery and for production environment) it will fully logged Bulk Operations.
5. Auto Growth option could be really a horrible if it is not configured properly and your database size is considerably large, lets say few hundred gigs or greater. If you’ve set Auto Growth option set on and they are in percentage and not in fixed size you would observe more spics in disk I/O and decrease in performance, read complete article here.
6. As we all know Group by or Order by will resort data and ordering result set, this will increase the space occupied, try to avoid this operation unless it is necessary. This way you would save up your space and increase in performance.
7. When you have transactional replication configured in your environment the transaction which are marked for replication but not replicated actually are lying in your T-Log and then distribution database which will certainly occupy reasonable amount of space. For any possible reason, see if something is wrong with your Transactional replication and rectify it.
8. This is the best way to keep your T-Log in shape, the more frequent i.e. 15 minutes. Backup Log will empty the T-Log file and thus reducing the size of log file.
Summery: Transaction Log, as name says records every transaction that is executed. When database is in full recovery model or frequent bulk operations happening it is most likely that your T-Log get filled quickly.
The above explanations and probable solution I’ve given came from my 10+ years of IT Industry experience while working on projects and my participations in various SQL Server forums. I do hope that this quick points will help you and save your time.
The cause which leads to T-Log to fill up, I am penning down some points I’ve recollected, they are:
1. Transactions are not committed
2. Transaction is not open but is active for long time
3. While creating / modifying Index
4. Bulk Operations
5. Auto Growth option of database files
6. Group by and Order by clause will fill up tempdb
7. Un-replicated transactions in T-Logs
8. T-Log backups are not taken for long
How to get rid off this messages (Solutions):
1. Ensure that application is written appropriately, and each transaction is wrapped between begin and commit transaction. This will not only helps to keep your data in consistent form but helps you to reduce the blockings.
2. This are the transactions that are not open but active for long time, means while user is sending some input to the database server his/her system got hung, rebooted or shutdown and network doesn’t send this information appropriately to the database thus, SQL Server understands this transaction is open but not active. This kind of sessions can be killed provided you’ve examine what this process is doing exactly by using DBCC Inputbuffer (SPID), be cautious while killing any process as it would take 4 times longer. * To know more on how Kill command works click here… especially when it rollback transaction
3. This is the most important point; creating or recreating an index would occupy much of the space as it is fully logged operation. When you run DBCC DBREINDEX it will drop existing index and recreate index (will recreate every index of your table if you won’t give name of index) . I would advise that you care fully examine output of DBCC SHOWCONTIG or sys.dm_db_index_physical_stats and based on that fact you can go ahead and defrag your index. Recreate your index only when required.
4. Although, bulk operations are minimally logged but it occupies the space certainly, in case your SQL Server is in full recovery mode (which is highly recommended for point-in-time recovery and for production environment) it will fully logged Bulk Operations.
5. Auto Growth option could be really a horrible if it is not configured properly and your database size is considerably large, lets say few hundred gigs or greater. If you’ve set Auto Growth option set on and they are in percentage and not in fixed size you would observe more spics in disk I/O and decrease in performance, read complete article here.
6. As we all know Group by or Order by will resort data and ordering result set, this will increase the space occupied, try to avoid this operation unless it is necessary. This way you would save up your space and increase in performance.
7. When you have transactional replication configured in your environment the transaction which are marked for replication but not replicated actually are lying in your T-Log and then distribution database which will certainly occupy reasonable amount of space. For any possible reason, see if something is wrong with your Transactional replication and rectify it.
8. This is the best way to keep your T-Log in shape, the more frequent i.e. 15 minutes. Backup Log will empty the T-Log file and thus reducing the size of log file.
Summery: Transaction Log, as name says records every transaction that is executed. When database is in full recovery model or frequent bulk operations happening it is most likely that your T-Log get filled quickly.
The above explanations and probable solution I’ve given came from my 10+ years of IT Industry experience while working on projects and my participations in various SQL Server forums. I do hope that this quick points will help you and save your time.
