I'm learning everything I can about backups and transaction logs and such. I want a little feedback on if I'm understanding this, so I have a conceptual question. First, here is what I observed:

I did a massive delete of data (using SQL Management studio, typed the delete command manually), so much that the trans log filled up. The query appeared to keep running even when the trans log got full. I then cancelled the query, and it showed cancelling.

I then did a trans log backup, to clear the trans log, followed by a full backup (while the query was still cancelling). I got the expected error that the trans log was full and backup couldn't happen. I waited until the query finally stopped, then was able to do another trans log backup, and this time the full backup worked.

All of this was done on a test DB, as part of a tutorial on doing backups, to help teach about what the transaction log does. I was told it would behave this way, and my observation matched what I was told as part of the tutorial.

My conceptual question:

The trans log wouldn't backup and clear while the query was cancelling because the data in the trans log was being used to undo the delete, but once the delete was undone, the trans log was freed up to be cleared. Is that correct?

Thanks in advance!


1 Answer 1


As Dan Guzman originally mentioned in a comment, your understanding of why the active portion of the transaction log couldn't be cleared is correct (it was being used for the large rollback).

There is one thing of interest that I think is worth mentioning related to this:

The query appeared to keep running even when the trans log got full. I then cancelled the query, and it showed cancelling.

Whenever log records are written to the transaction log, additional space is reserved to accommodate a rollback in case an error is encountered (or an explicit rollback is requested). This is documented in the MS Docs article SQL Server Transaction Log Architecture and Management Guide, specifically in the section on "Transaction Log Logical Architecture."

When the transaction log looked full, the query execution encountered and error and automatically started rolling back. Had you waited for this to complete, you would have seen this error message:

Msg 9002, Level 17, State 4, Line 1
The transaction log for database 'YourDatabaseName' is full due to 'ACTIVE_TRANSACTION'.


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.