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!
Best Answer
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:
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: