July 2, 2015 at 10:58 pm
Please let me know the benefit of breaking large transaction into multiple small transactions(means nesting of transactions)
July 3, 2015 at 2:21 am
Breaking a big transaction in smaller batches is not nesting transactions. The main benefit is less transaction log space used (the log can be truncated by checkpoints in simple recovery or by transaction log backups in full recovery).
Nesting transactions has the same exact effect of submitting a single big transaction.
-- Gianluca Sartori
July 3, 2015 at 3:04 am
Since SQL Server doesn't have nested transactions (just syntax which makes you think it does), there's no advantage. A transaction should be as small as possible while still ensuring the atomicity and isolation of the required code. If you make the transaction smaller than that, then you've potentially broken the code.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply