Lesson 5: Error Handling (TRY/CATCH) and Defensive DBA Scripts

Section 4 — Lesson 5: Error Handling (TRY/CATCH) and Defensive DBA Scripts

Welcome back. In production, SQL scripts are not judged by how they behave when everything goes right.
They are judged by how safely they behave when something goes wrong.

DBAs write scripts that must survive real-world conditions:
blocking, deadlocks, permission issues, missing objects, timeouts, data surprises, and partial failures.
Your job is to build scripts that fail in a controlled way, leave the database consistent, and leave evidence
for troubleshooting.

Lesson description: You will learn SQL Server’s TRY/CATCH syntax, what errors can and cannot be caught,
how to capture useful error details, how to combine TRY/CATCH with transactions for safe rollback,
how to implement “pre-flight” validation checks (defensive scripting),
how to avoid partial changes, how to log outcomes, and how DBAs structure scripts so they are repeatable, auditable,
and safe to run under pressure.

1) What TRY/CATCH Is (And What It Is Not)

TRY/CATCH in T-SQL allows you to:

• attempt work inside BEGIN TRY ... END TRY
• handle errors inside BEGIN CATCH ... END CATCH
• capture error metadata (message, number, state, line, procedure)
• roll back transactions to prevent partial changes
• return consistent, clean output to the caller (or to a job log)

TRY/CATCH does not magically prevent all failures. Your goal is:
fail safely, fail clearly, and leave evidence.

2) Basic TRY/CATCH Syntax

BEGIN TRY
    -- Work goes here
END TRY
BEGIN CATCH
    -- Error handling goes here
END CATCH;

Inside the CATCH block, SQL Server exposes functions that tell you what happened.

Core error functions

• ERROR_NUMBER() — error code
• ERROR_MESSAGE() — text message
• ERROR_SEVERITY() — severity level
• ERROR_STATE() — state (more detail)
• ERROR_LINE() — line number where error occurred
• ERROR_PROCEDURE() — stored procedure name (if applicable)

3) The DBA “Error Capture Block” (Standard Pattern)

DBAs capture all useful metadata and return it consistently. This pattern is job-ready.

BEGIN TRY
    -- Example: harmless query
    SELECT TOP (1) name FROM sys.databases;
END TRY
BEGIN CATCH
    SELECT
        ERROR_NUMBER()    AS ErrorNumber,
        ERROR_SEVERITY()  AS ErrorSeverity,
        ERROR_STATE()     AS ErrorState,
        ERROR_PROCEDURE() AS ErrorProcedure,
        ERROR_LINE()      AS ErrorLine,
        ERROR_MESSAGE()   AS ErrorMessage;
END CATCH;

DBA reason: If a SQL Agent job fails, you want the log to show exact error details, not “it failed.”

4) Defensive Scripting: Pre-Flight Checks Before You Touch Anything

A defensive DBA script verifies assumptions before it runs. This prevents messy partial failures.
Examples of assumptions:

• the database exists and is online
• the table exists
• required columns exist
• you are in the correct database
• you have required permissions
• the target row(s) exist
• the script is safe to re-run (idempotent)

4A) Example: Check database state (safe guard)

DECLARE @DbName sysname = DB_NAME();

IF DATABASEPROPERTYEX(@DbName, 'Status') <> 'ONLINE'
BEGIN
    THROW 50001, 'Database is not ONLINE. Aborting script.', 1;
END;

DBA note: THROW stops execution cleanly and can be caught by TRY/CATCH.

5) THROW vs RAISERROR (Modern DBA Guidance)

Both exist, but for modern SQL Server scripting:

• Prefer THROW for re-throwing errors and raising custom errors simply.
• RAISERROR is older; you will still see it in legacy scripts.

5A) Raise a clean custom error with THROW

IF 1 = 1
BEGIN
    THROW 50002, 'Pre-check failed: required condition not met.', 1;
END;

5B) Re-throw the original error inside CATCH

This preserves original error info for logs/callers.

BEGIN TRY
    -- Force an error (demo)
    SELECT 1 / 0;
END TRY
BEGIN CATCH
    -- Log details first
    SELECT ERROR_NUMBER() AS ErrorNumber, ERROR_MESSAGE() AS ErrorMessage;

    -- Re-throw original error
    THROW;
END CATCH;

6) TRY/CATCH + Transactions: Prevent Partial Changes

The most important defensive DBA pattern is:

1. start a transaction
2. do work
3. commit only if everything succeeded
4. rollback in CATCH if anything failed

6A) Standard “safe transaction” template

SET NOCOUNT ON;

BEGIN TRY
    BEGIN TRAN;

    -- Work goes here (INSERT/UPDATE/DELETE or schema changes)
    -- Example: placeholder query
    SELECT TOP (1) * FROM sys.objects;

    COMMIT TRAN;
END TRY
BEGIN CATCH
    IF XACT_STATE() <> 0
        ROLLBACK TRAN;

    SELECT
        ERROR_NUMBER()   AS ErrorNumber,
        ERROR_MESSAGE()  AS ErrorMessage,
        ERROR_LINE()     AS ErrorLine,
        XACT_STATE()     AS XactState;

    THROW; -- optional: bubble error up to caller/job
END CATCH;

What XACT_STATE means (DBA must know)

• 0 = no active transaction
• 1 = active transaction, can commit
• -1 = active transaction but uncommittable (must rollback)

DBA rule: If a CATCH happens and XACT_STATE is not 0, rollback.

7) “Fail Fast” Scripts: Stop If the Impact Is Too Large

DBAs protect production by adding guardrails to prevent accidental mass updates/deletes.
A job-ready script validates row counts before committing.

7A) Rowcount safety pattern (example)

This is a pattern you will use often:

DECLARE @MaxAllowedRows int = 100;  -- guardrail
DECLARE @RowsAffected  int;

BEGIN TRY
    BEGIN TRAN;

    -- Example only: pretend update
    -- UPDATE dbo.SomeTable SET ... WHERE ...
    -- SET @RowsAffected = @@ROWCOUNT;

    SET @RowsAffected = 150; -- demo

    IF @RowsAffected > @MaxAllowedRows
    BEGIN
        THROW 50003, 'Safety stop: too many rows would be affected. Aborting.', 1;
    END;

    COMMIT TRAN;
END TRY
BEGIN CATCH
    IF XACT_STATE() <> 0
        ROLLBACK TRAN;

    SELECT ERROR_NUMBER() AS ErrorNumber, ERROR_MESSAGE() AS ErrorMessage;
END CATCH;

DBA mindset: Production safety > “getting it done quickly.”

8) Building “Script Output Evidence” (So You Can Prove What Happened)

Professional DBA scripts include evidence:

• start time and end time
• who ran the script
• target database and server
• what actions were performed
• row counts affected
• success/failure status

8A) Evidence header template

DECLARE @StartTime datetime2(3) = SYSDATETIME();

SELECT
    @StartTime                 AS ScriptStartTime,
    SUSER_SNAME()              AS ExecutedBy,
    @@SERVERNAME               AS ServerName,
    DB_NAME()                  AS DatabaseName,
    APP_NAME()                 AS AppName,
    HOST_NAME()                AS HostName;

8B) Evidence footer template

SELECT
    SYSDATETIME() AS ScriptEndTime,
    DATEDIFF(SECOND, @StartTime, SYSDATETIME()) AS DurationSeconds;

DBA value: This makes your work auditable and easy to troubleshoot later.

9) Defensive Patterns DBAs Use Often (Practical Checklist)

• SET NOCOUNT ON to keep outputs clean (especially in procedures/jobs).
• Explicit transactions with rollback in CATCH.
• Rowcount guardrails (fail fast if too many rows affected).
• Object existence checks (OBJECT_ID, sys.objects).
• Parameter validation (NULL/empty checks, range checks).
• Idempotency (script can be safely re-run without creating duplicates).
• Evidence output (who/when/where/what).

10) Practice (Job-Ready Exercises)

Save each as a script in your “DBA script pack.”

1. Write a TRY/CATCH script that intentionally raises a custom error with THROW and logs the error metadata.
2. Create a transaction wrapper template (BEGIN TRAN → COMMIT; ROLLBACK in CATCH) and test it with a harmless SELECT.
3. Add an object existence check (e.g., verify a table exists before selecting from it). If missing, THROW a custom error.
4. Implement a rowcount guardrail template. Simulate a large rowcount and confirm the script aborts safely.
5. Add evidence header/footer outputs to your templates (start time, executed by, server, database, end time).

Lesson Summary

• TRY/CATCH allows DBAs to handle errors predictably and capture useful metadata.
• Defensive scripts validate assumptions before running and include guardrails to prevent disasters.
• Transactions + rollback in CATCH prevent partial changes and keep data consistent.
• THROW is the modern way to raise and re-throw errors for clean job logging.
• Professional scripts output evidence (who/when/where/what) to support auditing and troubleshooting.

Next Lesson: Transactions in T-SQL (BEGIN/COMMIT/ROLLBACK Patterns)

Next, you will go deeper into transaction behavior: isolation, nested transactions,
how locks appear, what “uncommittable” means, and the exact patterns DBAs use to protect production changes.