Overview
T-SQL TRY/CATCH captures errors inside batches. Combined with BEGIN TRANSACTION, you roll back on failure and commit on success.
Check @@TRANCOUNT and XACT_STATE() in nested scenarios.
Implementation
Pattern: BEGIN TRY; BEGIN TRAN; -- statements; COMMIT; END TRY BEGIN CATCH; IF @@TRANCOUNT > 0 ROLLBACK; THROW; END CATCH
Use SET XACT_ABORT ON for automatic rollback on errors in some cases.
When implementing guidance from SQL Server Handle Transaction and Try Catch, start in a controlled environment that mirrors production versions of operating systems, runtimes, and network policies. Capture a baseline before changes: export configs, snapshot VMs, or tag releases in source control so rollback stays straightforward if behavior regresses.
Document prerequisites, expected outcomes, and verification steps in a short runbook. Automated checks—smoke tests, health endpoints, or query validations—catch regressions early when platforms receive patches. Security belongs in every workflow: apply least privilege, rotate secrets, and review audit logs after deployment.
If results differ across machines, compare environment variables, permission models, time zones, and regional settings. Intermittent issues often trace to caching layers, stale DNS, or duplicated services bound to the same port.
Example
BEGIN TRY
BEGIN TRAN;
UPDATE Accounts SET Balance -= 100 WHERE Id = 1;
UPDATE Accounts SET Balance += 100 WHERE Id = 2;
COMMIT TRAN;
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0 ROLLBACK TRAN;
THROW;
END CATCH;Tips
- Keep transactions short.
- Deadlocks: retry with backoff.
- Log errors to a table in CATCH.
- Avoid user input in dynamic SQL.
- Re-verify after reboots, certificate renewals, or failover exercises.
- Align monitoring and alerts with the failure modes described in this guide.
- Keep vendor documentation links handy for breaking changes between versions.
- Pair automation with a manual spot check during initial production rollout.