uncommitted transaction prevents backup of transaction logs

Question

question

ossdba asked Jul 2, '13

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?

backup transaction

1

10 |1200

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

ossdba commented · Jul 02, 2013 at 04:53 PM

Thanks a lot for your answers/tips!

0 ·

Answer 1 · 2013-07-02T14:58:07Z

sqlnubi answered Jul 2, '13

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

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

Answer 2 · 2013-07-02T14:36:06Z

KenJ answered Jul 2, '13

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

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

Answer 3 · 2013-07-02T14:36:54Z

ThomasRushton answered Jul 2, '13

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

10 |1200

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

KenJ commented · Jul 02, 2013 at 02:43 PM

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 ·

ThomasRushton ♦♦ commented · Jul 02, 2013 at 02:53 PM

Good point, @KenJ. Brain not working. Removed wrong material, and left references. <fx: hangs head in shame>

0 ·

question