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.
Leave a Reply Cancel reply
Upcoming Presentations
Next Presentation – May 14, 2022 at 5:30 CDT
Search
Tweets
- @sqlcheesecake @AMtwo Price just dropped $15M zillow.com/homedetails/19… 1 week ago
- #sqlhelp Update: The delegation for the IIS Service was deleted and recreated and things look good. 1 week ago
- #sqlhelp Issue adding delegation to MSSQLSvc for an AG listener for the IIS service. More details here: sqlservercentral.com/forums/topic/c… 1 week ago
- @sqlagentman But it's anti-bacterial body lotion, right? 1 week ago
- @sqL_handLe When is the mothership coming? Really. I need to know. 1 week ago
Recent Posts
Archives
- March 2022
- November 2021
- November 2019
- November 2018
- October 2018
- June 2018
- April 2018
- November 2017
- October 2017
- August 2017
- January 2017
- October 2016
- August 2016
- July 2016
- October 2015
- May 2015
- October 2014
- May 2014
- April 2014
- March 2014
- February 2014
- January 2014
- November 2013
- October 2013
- September 2013
- August 2013
- July 2013
- May 2013
- April 2013
- February 2013
- January 2013
- November 2012
- October 2012
- August 2012
- July 2012
- June 2012
- May 2012
- April 2012
- March 2012
Categories
- #sqlfamily
- #sqlhelp
- Administration
- Availability Groups
- Azure
- Azure Data Studio (ADS)
- Azure SQL Managed Instance
- Black Sabbath
- Blogging
- Career
- Central Management Server
- Corruption
- CSSUG
- DMO
- Driver Has No Cash
- Extended Events
- Immersion Events
- Infonomics
- Job Hunting
- Managed Instance
- Mirroring
- PASS
- PASS Summit 2012
- PASS Summit 2013
- PASS Summit 2018
- PASS Summit 2019
- PerfMon
- Policy-Based Management
- PowerShell
- Presenting
- Query Store
- Restores
- sp_whoisactive
- SQL Saturday
- SQL Server
- SQL Server 2012
- SQL Server 2016
- SQL Server Agent
- SQL Server Database Mail
- SQL Server Transaction Log
- SQL Server Transaction Log
- SQL Waits
- SQLFriends
- SQLskills
- T-SQL
- T-SQL Tuesday
- Ukulele
- Uncategorized
- Working From Home
Calendar
S | M | T | W | T | F | S |
---|---|---|---|---|---|---|
1 | 2 | 3 | 4 | |||
5 | 6 | 7 | 8 | 9 | 10 | 11 |
12 | 13 | 14 | 15 | 16 | 17 | 18 |
19 | 20 | 21 | 22 | 23 | 24 | 25 |
26 | 27 | 28 |
Tags
Availability Groups
Azure
Azure SQL Managed Instance
Blogging
Central Management Server
CSSUG
DMO
Extended Events
Immersion Events
Infonomics
Managed Instance
PASS
PowerShell
Rock
SQL
SQLFamily
SQLFriends
SQL Saturday
SQL Saturday Oregon
SQL Server
SQL Server 2012
SQL Server 2016
SQL Server Agent
SQLskills
T-SQL
T-SQL Tuesday
TLS
xp_cmdshell
Tags
Availability Groups
Azure
Azure SQL Managed Instance
Blogging
Central Management Server
CSSUG
DMO
Extended Events
Immersion Events
Infonomics
Managed Instance
PASS
PowerShell
Rock
SQL
SQLFamily
SQLFriends
SQL Saturday
SQL Saturday Oregon
SQL Server
SQL Server 2012
SQL Server 2016
SQL Server Agent
SQLskills
T-SQL
T-SQL Tuesday
TLS
xp_cmdshell
My Presentations
SQL Server
Follow skreebydba via e-mail
Join 1,165 other subscribers
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