question

ossdba avatar image
ossdba asked

uncommitted transaction prevents backup of transaction logs

Can someone give me an example how uncommitted transaction prevents backup of transaction logs (scheduled to run every 15 min), please?
backuptransaction
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Thanks a lot for your answers/tips!
0 Likes 0 ·
sqlnubi avatar image
sqlnubi answered
You can still backup the transaction log regardless of an open transaction, that is exactly what happens with each log backup. However the long running transaction will keep the VLF's in use and prevent them from being truncated and reused. If all the VLF's are spanned by that transaction you will see continued log growth. Depending on the transaction and the amount of free space that could result in an out of disk space issue.
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

KenJ avatar image
KenJ answered
Uncommitted transactions don't prevent transaction log backups, they prevent transaction log reuse, often leading to log growth: [ http://www.sqlskills.com/blogs/paul/search-engine-qa-1-running-out-of-transaction-log-space/][1] [1]: http://www.sqlskills.com/blogs/paul/search-engine-qa-1-running-out-of-transaction-log-space/
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

ThomasRushton avatar image
ThomasRushton answered
You might want to read [SQL Server Transaction Log Management][1] from Red Gate. Similarly, you might also want to have a skim through some of the [SQL Skills articles on VLFs][2]. [1]: http://www.red-gate.com/community/books/sql-server-transaction-log-management [2]: http://www.sqlskills.com/search/?q=VLF
2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

I would suggest that the active VLF is also backed up (not just VLFs that are no longer being filled) as it may have a mix of both uncommitted and committed transactions that need to be rolled back / committed during restore/recovery
0 Likes 0 ·
Good point, @KenJ. Brain not working. Removed wrong material, and left references. <fx: hangs head in shame>
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.