About
I’ve been working as a SQL Server DBA for more than four years. I enjoy scripting T-SQL and learning everything I can about SQL Server internals. When I’m not working with SQL, I enjoy reading, math, history, White Sox baseball and volunteering at The Art Institute of Chicago.
Hi Frank,
Thank you for your enjoyable and informative presentation. I think I found the answer regarding nested transactions. The following topics are taken from Books Online.
Nesting Transactions
ms-help://MS.SQLCC.v10/MS.SQLSVR.v10.en/s10de_1devconc/html/8cca105f-6d39-491f-9a85-e11cd4220cc1.htm
Committing inner transactions is ignored by the SQL Server Database Engine. The transaction is either committed or rolled back based on the action taken at the end of the outermost transaction. If the outer transaction is committed, the inner nested transactions are also committed. If the outer transaction is rolled back, then all inner transactions are also rolled back, regardless of whether or not the inner transactions were individually committed.
If a ROLLBACK WORK or ROLLBACK TRANSACTION statement without a transaction_name parameter is executed at any level of a set of nested transaction, it rolls back all of the nested transactions, including the outermost transaction.
Using DML Triggers That Include COMMIT or ROLLBACK TRANSACTION
ms-help://MS.SQLCC.v10/MS.SQLSVR.v10.en/s10de_1devconc/html/d7d7fd3f-50b7-4f8e-b03f-195844aee441.htm
If triggers that include ROLLBACK TRANSACTION statements are fired from within a user-defined transaction, ROLLBACK TRANSACTION rolls back the whole transaction.
When triggers that include COMMIT or ROLLBACK TRANSACTION statements are executed from a batch, they cancel the whole batch.
We do not recommend placing COMMIT TRANSACTION or COMMIT WORK statements in a trigger.
Rollbacks and Commits in Stored Procedures and Triggers
ms-help://MS.SQLCC.v10/MS.SQLSVR.v10.en/s10de_1devconc/html/650105c1-32fd-4f16-8082-f391c42c3fb0.htm
If @@TRANCOUNT has a different value when a stored procedure finishes than it had when the procedure was executed, an informational error (266) occurs.
(RA: That is if there was a ROLLBACK, or COMMIT without a BEGIN TRANSACTION in the stored procedure.)
If the trigger completes execution and @@TRANCOUNT = 0, error 3609 occurs and the batch is terminated. If a BEGIN TRANSACTION statement is issued in a trigger, it creates a nested transaction. In this situation, when a COMMIT TRANSACTION statement is executed, the statement will apply only to the nested transaction.
Intentionally nesting transactions doesn’t buy you anything. This capability is available to allow stored procedures, triggers, or batches (and probably functions) containing a transaction to call other stored procedures or triggers containing a transaction. It should not be considered as a useful programming construct to allow you to rollback sections inside a transaction. For that savepoints are available.
I think this answers our question. Thanks again for your well prepared and informative talk.
Robert
Robert,
Thanks for sending this along and thanks for putting it together.
Frank
Frank,
Enjoyed your Automate Restore presentation at Sql Saturday in Iowa City. I am unable to run
the Ps script testdatabaserestore successfully. I changed all the c:\user paths to my local path and changed the instance name in the script. I get no errors or anything – it just returns to the prompt without doing anything. I created a full backup of a database to make sure I had something there.
Do you have any suggestions on what may be the issue? I’m am learning PowerShell.
Thanks,
Dave
Dave, sorry for the late reply. Executing the script creates a cmdlet called Test-DatabaseRestore. Once you’ve run it, you can run Test-DatabaseRestore from a Powershell prompt.
Let me know if this works for you. If not, e-mail me at skreebydba (at) gmail (dot) com.
Thanks,
Frank