Home > DMO, Immersion Events, SQL Server, SQL Server Transaction Log, SQLskills, T-SQL > SQL Server Log Space Reservation – Part I

SQL Server Log Space Reservation – Part I

My biggest challenge in blogging about the SQLskills Internals Immersion Event is picking a topic to start with. I am going to choose log file space consumption because of an issue that occurred this week in our test environment.

An application ran a job that inserted over 400 million in a single transaction. After the job ran for more than 24 hours, the application requested the job be killed because it was “taking too long.” Go figure. The app was advised the rollback could take as long as the initial job run, and they decided to have the process killed. The rollback ended up taking about the same amount of time. When SQL Server rolls back a transaction, it generates and executes an anti-operation for each record in the log. In the case of 400 million inserts, 400 million deletes needed to be generated and executed.

While I was aware of this behavior going into the class, I learned that this scenario is even more costly because SQL Server reserves space in the log for any potential rollback activity. This means all of the log records for the inserts are written into the log and SQL reserves additional space for the rollback, more than doubling the amount of space needed. You can see the amount space reserved by a transaction in the sys.dm_tran_database_transactions dynamic management object in column database_transaction_log_space_reserved. The reserved space is released once the transaction commits. Additionally, because all of the log records for the large transaction are needed for a rollback, SQL Server cannot reuse the log file and it will continue to grow.

Both of these problems can be solved by running the single transaction in smaller batches. Committing more frequently will release the reserved undo space. It will also increase the probability SQL Server can reuse the existing log file. In my next post I will compare the log file use and reservation single long-running transaction to shorter, batched transactions performing the same activity.

Leave a Reply

%d bloggers like this: