Get an email when SyteLine is blocked: a simple SQL Server blocking alert

This is the alerting companion to SyteLine freezing for everyone? Find the SQL head blocker, which explains how to find and safely deal with the blocker once you know about it.

Why this is worth 30 minutes

In Why a SyteLine task runs for days, a packing slip run with a mistyped range blocked invoicing for days. Small reports kept working, so nothing looked wrong until invoicing was noticed. SQL Server knew about the blocking the whole time. It just had no way to tell anyone.

This alert gives it one.

Before you start

You'll need:

• SQL Server Agent running. It's not included in SQL Server Express. SyteLine's background processing already relies on it.
• Database Mail configured, with a mail profile that can send to your team. Ask your DBA. If it's already used for backup alerts, reuse that profile.
• Permission to create Agent jobs, usually a DBA.

The check

This is the read-only query the job runs. Try it on its own first:

-- sessions on the SyteLine database blocked for longer than the threshold (read-only)
DECLARE @db sysname = N'SyteLine_App';     -- your SyteLine database name
DECLARE @threshold_ms int = 10 * 60 * 1000; -- 10 minutes

SELECT  r.session_id            AS blocked_spid,
        r.blocking_session_id   AS blocking_spid,
        r.wait_time / 1000      AS blocked_seconds,
        r.wait_type,
        bs.login_name           AS blocking_login,
        bs.host_name            AS blocking_host,
        bs.program_name         AS blocking_program,
        LEFT(t.text, 400)       AS blocked_sql
FROM    sys.dm_exec_requests  AS r
JOIN    sys.dm_exec_sessions  AS bs ON bs.session_id = r.blocking_session_id
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS t
WHERE   r.blocking_session_id <> 0
  AND   r.database_id = DB_ID(@db)
  AND   r.wait_time >= @threshold_ms
ORDER BY r.wait_time DESC;

The job step

Use this as the T-SQL step of an Agent job. Replace the database name, mail profile and recipients:

SET NOCOUNT ON;

DECLARE @db sysname = N'SyteLine_App';
DECLARE @threshold_ms int = 10 * 60 * 1000;
DECLARE @body nvarchar(max);
DECLARE @sep  nvarchar(10) = CONCAT(CHAR(13), CHAR(10), CHAR(13), CHAR(10));

SELECT @body = STRING_AGG(
         CONCAT('Blocked SPID ', r.session_id,
                ' by SPID ', r.blocking_session_id,
                ' for ', r.wait_time / 1000, ' s',
                ' | blocker: ', bs.login_name, ' on ', bs.host_name,
                ' (', bs.program_name, ')',
                CHAR(13), CHAR(10), '   ', LEFT(t.text, 300)),
         @sep)
FROM    sys.dm_exec_requests  AS r
JOIN    sys.dm_exec_sessions  AS bs ON bs.session_id = r.blocking_session_id
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS t
WHERE   r.blocking_session_id <> 0
  AND   r.database_id = DB_ID(@db)
  AND   r.wait_time >= @threshold_ms;

IF @body IS NOT NULL
    EXEC msdb.dbo.sp_send_dbmail
         @profile_name = N'DBA Alerts',                -- your Database Mail profile
         @recipients   = N'erp-support@yourcompany.com',
         @subject      = N'SyteLine: session blocked for over 10 minutes',
         @body         = @body;

STRING_AGG needs SQL Server 2017 or later. On older versions, build the message with a FOR XML PATH('') concatenation, or simply email a count and check the details by hand.

Create the job

In SQL Server Management Studio, under SQL Server Agent > Jobs:

1. New Job. Name it something obvious, such as SyteLine - blocking alert.
2. Steps: add one T-SQL step running the script above in the master or msdb database.
3. Schedule: every 5 minutes, all day. If nights are full of long jobs that you're happy to have blocking, limit it to business hours.
4. Notifications: also tick email the operator if the job fails, so you know if the alert itself breaks.

Tuning it

• Threshold. Ten minutes is a sensible start. If month-end posting legitimately blocks for longer, raise it for those nights rather than ignoring the emails.
• Noise. If one known process alerts every night, deal with it. Reschedule it or fix it. An alert people learn to ignore is worse than none.
• Repeat emails. The job emails every five minutes while blocking lasts. That's deliberate, because it keeps the problem visible. If it's too much, log alerts to a table and only email when a new blocking SPID appears.

When the email arrives

Don't kill anything just because the email says so. Follow the same steps as in the wrong-range article:

1. Identify the blocking SPID from the email.
2. Confirm what it is: input buffer, host, start time, and the matching task in Active Background Tasks.
3. Only if it's clearly the wrong task, and with the system administrator agreeing, KILL it and let the rollback finish.
4. Fix the cause, such as a bad parameter or a slow query, and re-run.

The alert's job is to make sure someone looks. Deciding what to do is still a human call.

FAQ

How do I get an alert when SQL Server sessions are blocked?

Run a SQL Server Agent job every few minutes that checks sys.dm_exec_requests for sessions blocked longer than a threshold, and email the details with Database Mail.

Should the blocking alert kill the blocking session automatically?

No. Automatically killing sessions can roll back legitimate work, such as posting. Alert, then let a person confirm before acting.

What threshold should I use for blocking alerts on SyteLine?

Start at about ten minutes and adjust to how long your normal heavy processes, like posting, legitimately take.

Does this work on SQL Server Express?

No. SQL Server Express doesn't include SQL Server Agent. You'd need another scheduler, such as Windows Task Scheduler running sqlcmd.