Self-Contained Transaction Scripts in SQL Server: the @debug_only Pattern
In a companion post I looked at COMMIT, ROLLBACK, and autocommit from a PostgreSQL angle, and along the way I leaned on a SQL Server habit a lot of maintenance scripts share: wrap the work in BEGIN TRANSACTION, run down to a SELECT, eyeball the grid, then highlight COMMIT or ROLLBACK by hand. I promised to show how I actually prefer to write these scripts, because that highlight-and-run habit has a failure mode I would rather not rely on.
The problem is not the idea of deciding at the end. The problem is that the decision depends on a transaction sitting open in an interactive session, waiting for a human. Forget to finish it, get pulled into a meeting, close the laptop, and you have left a transaction open and holding locks. Anyone who has watched a maintenance window stall behind a forgotten BEGIN TRAN knows the feeling.
So here is the pattern I use instead. It does all the work, shows me exactly what changed, and commits or rolls back on its own, all in a single batch that never leaves a transaction open.
The Same Warehouse, Done Safely
I will reuse the scenario from the companion post: move stock of one product from one warehouse to another. Validate first, then do the work inside one transaction, then decide based on a flag.
|
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 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 |
USE [Warehouse]; GO SET XACT_ABORT ON; SET NOCOUNT ON; DECLARE @debug_only bit = 1; /* 1 = do the work then ROLL BACK; 0 = COMMIT for real */ DECLARE @product_id int = 1001; DECLARE @from_wh int = 1; DECLARE @to_wh int = 2; DECLARE @qty int = 50; DECLARE @available int; /* ---- Validate before touching anything ---- */ SELECT @available = [quantity] FROM [dbo].[inventory] WHERE [warehouse_id] = @from_wh AND [product_id] = @product_id; IF @available IS NULL BEGIN THROW 50000, N'No inventory row for that product in the source warehouse.', 1; END; IF @available < @qty BEGIN THROW 50000, N'Insufficient stock in the source warehouse.', 1; END; /* ---- Show the before picture ---- */ SELECT [warehouse_id], [product_id], [quantity] FROM [dbo].[inventory] WHERE [product_id] = @product_id AND [warehouse_id] IN (@from_wh, @to_wh); /* ---- Do the work inside one named, marked transaction ---- */ PRINT N'[stock_transfer] starting.'; BEGIN TRANSACTION [stock_transfer] WITH MARK N'warehouse stock transfer'; UPDATE [dbo].[inventory] SET [quantity] = [quantity] - @qty WHERE [warehouse_id] = @from_wh AND [product_id] = @product_id; UPDATE [dbo].[inventory] SET [quantity] = [quantity] + @qty WHERE [warehouse_id] = @to_wh AND [product_id] = @product_id; /* ---- Show the after picture (still inside the transaction) ---- */ SELECT [warehouse_id], [product_id], [quantity] FROM [dbo].[inventory] WHERE [product_id] = @product_id AND [warehouse_id] IN (@from_wh, @to_wh); /* ---- Decide, based on the flag, and only if the transaction is still alive ---- */ IF @debug_only = 0 BEGIN IF XACT_STATE() <> 0 AND @@TRANCOUNT > 0 BEGIN COMMIT TRANSACTION [stock_transfer]; PRINT N'[stock_transfer] committed on ' + CONVERT(nvarchar(30), GETDATE(), 120) + N'.'; END; END; ELSE BEGIN IF XACT_STATE() <> 0 AND @@TRANCOUNT > 0 BEGIN ROLLBACK TRANSACTION [stock_transfer]; PRINT N'DRY RUN - [stock_transfer] rolled back.'; END; END; |
Run it with @debug_only = 1 and it does the full transfer, prints the before and after grids, then rolls everything back. Flip it to 0 and the identical script commits. Either way the batch finishes with no open transaction left behind, whether you run it in SSMS or pipe it through sqlcmd.
Why Each Piece Is There
SET XACT_ABORT ON
This is the keystone, and it is the closest SQL Server gets to PostgreSQL’s “any error aborts the whole transaction” behavior. With XACT_ABORT on, if any statement raises a run-time error, SQL Server aborts the batch and rolls back the entire transaction automatically. Without it, some errors only abort the current statement and leave the transaction open and committable, which is exactly how a partial change sneaks through. For a maintenance script that changes data, I want all-or-nothing, so XACT_ABORT ON goes at the top every time. The SET XACT_ABORT documentation lists the exact behavior.
Because XACT_ABORT handles the error path for me, I do not wrap the body in TRY/CATCH here. A TRY/CATCH is worth adding when you want to log the error or rethrow it with context, but for a one-off it adds noise without changing the outcome: on error, the transaction is already rolled back.
The @debug_only flag
This is the part that replaces the commented-out COMMIT. Instead of editing the script to choose commit or rollback, I set one bit. When it is 1, the script runs end to end and then rolls back, so I can read the after grid and the row counts knowing nothing was kept. When I am satisfied, I set it to 0 and run the same text again to apply. Nothing is commented in or out, and there is no way to leave the decision half-made.
XACT_STATE() and @@TRANCOUNT
Before committing or rolling back, the script checks two things. @@TRANCOUNT > 0 confirms a transaction is actually open. XACT_STATE() reports whether that transaction is healthy:
| Value | Meaning |
|---|---|
| 1 | An active, committable transaction is in progress |
| 0 | There is no open transaction |
| -1 | A transaction is open but doomed – it can only be rolled back, not committed |
Checking XACT_STATE() <> 0 AND @@TRANCOUNT > 0 means the script only tries to commit or roll back something that genuinely exists. With XACT_ABORT ON, a mid-script failure has usually already rolled the transaction back before this block runs, so the guard quietly does nothing rather than raising a second error about a missing transaction. The XACT_STATE documentation covers the three return values, including when a transaction becomes doomed.
The named, marked transaction
BEGIN TRANSACTION [stock_transfer] WITH MARK N'...' does two small but useful things. The name makes the commit and rollback statements self-documenting and ties the log messages to a specific operation. The WITH MARK clause writes a named mark into the transaction log, which gives you a recovery target: you can restore a copy of the database STOPBEFOREMARK or STOPATMARK to land immediately before or after this exact change. On a real change I also take a transaction-log backup just before the BEGIN TRANSACTION as a restore point, so the mark has something to anchor to. The BEGIN TRANSACTION documentation describes the WITH MARK option.
Making the Output Show Up Immediately
The script above uses PRINT for its progress messages, which is fine for a script that finishes quickly. One thing to know: PRINT output is buffered and may not appear until the batch ends or the buffer fills, so for a long-running script the messages can arrive in a clump at the end rather than as each step completes.
If you want each message the moment it happens, use RAISERROR at a low severity with WITH NOWAIT, which flushes immediately:
|
1 |
RAISERROR (N'[stock_transfer] starting.', 10, 1) WITH NOWAIT; |
Severity 10 is informational, so this prints a message without raising an error or affecting the transaction. This is the practical equivalent of PostgreSQL’s RAISE NOTICE. Microsoft now steers new error-raising code toward THROW, but THROW cannot send informational messages or flush with NOWAIT, so RAISERROR (..., 10, 1) WITH NOWAIT remains the right tool for immediate progress output, while THROW is the right tool for actually raising an error (as in the validation checks above). Both are described in the RAISERROR and THROW documentation.
Running It Unattended
The whole point of this shape is that it survives being run as a file. Pipe it through sqlcmd and there is no interactive session to babysit:
|
1 |
sqlcmd -S myserver -d Warehouse -i stock-transfer.sql -b |
If the script succeeds with @debug_only = 0, it commits before the connection closes. If a statement fails, XACT_ABORT ON rolls the transaction back and the -b flag makes sqlcmd exit with a non-zero code so a scheduler or wrapper can notice. If you leave @debug_only = 1, it rolls back on purpose. In none of those cases is a transaction left open after the batch ends, which is the property the highlight-and-run habit cannot promise.
The Short Version
The commented-out COMMIT habit and this pattern are trying to do the same thing: let a human decide whether a change sticks. The difference is what they lean on.
| Habit | This pattern |
|---|---|
| Decide by editing the script (comment in COMMIT or ROLLBACK) | Decide by setting one @debug_only bit |
| Transaction stays open in an interactive session until a human acts | Whole batch runs and resolves itself, open to close |
| Forgetting to finish leaves locks held | Nothing is left open after the batch ends |
Error handling depends on XACT_ABORT being set anyway |
XACT_ABORT ON is part of the template |
| Commit or rollback issued blindly | XACT_STATE() and @@TRANCOUNT guard the decision |
It is a few more lines than a commented-out COMMIT, and worth it. The script tells me what it did, keeps nothing unless I say so, and never asks me to remember to close a transaction.
If you have your own house style for safe maintenance scripts, I would like to compare notes. You can find me on Bluesky and LinkedIn.
References
- SET XACT_ABORT (Transact-SQL) – how errors abort the batch and roll back the transaction
- XACT_STATE (Transact-SQL) – the 1 / 0 / -1 return values and doomed transactions
- @@TRANCOUNT (Transact-SQL) – transaction nesting depth
- BEGIN TRANSACTION (Transact-SQL) – named transactions and the WITH MARK recovery option
- RAISERROR (Transact-SQL) – informational messages and WITH NOWAIT
- THROW (Transact-SQL) – raising errors in modern T-SQL
- sqlcmd utility – running scripts unattended, including the -b exit-on-error flag