# SQL Server Blocking and Deadlock Diagnostic Script Generator

> Source: https://netray.co/tools/sql-server-blocking-and-deadlock-diagnostic-script-generator
> Publisher: Netray (netray.co), AI and integration engineering for ERP and manufacturing systems.
> Section: Free Tools

## Short answer

Generate T-SQL to list blocked sessions, find the head blocker and its SQL text, spot idle open transactions and read recent deadlock graphs.

## Category

developer

## Erp

SQL Server based ERPs

## Audience

- ERP administrator
- DBA
- ERP analyst

## Tool

**Kind:** template

**Output:** code

**Language:** sql

**Fields:**

#### ERP database name

**Key:** db

**Type:** text

**Default:** ERP_PROD

#### Minimum wait to show (ms)

**Key:** min_wait_ms

**Type:** number

**Default:** 5000

#### Idle open transaction threshold (minutes)

**Key:** idle_minutes

**Type:** number

**Default:** 10

#### Deadlock lookback (hours)

**Key:** hours_back

**Type:** number

**Default:** 24

**Blocks:**

#### 1. Who is blocked and by whom

**Body:** SELECT
    r.session_id,
    r.blocking_session_id,
    r.wait_type,
    r.wait_time AS wait_ms,
    DB_NAME(r.database_id) AS database_name,
    s.login_name,
    s.host_name,
    s.program_name,
    SUBSTRING(t.text, (r.statement_start_offset / 2) + 1,
        ((CASE r.statement_end_offset WHEN -1 THEN DATALENGTH(t.text) ELSE r.statement_end_offset END
          - r.statement_start_offset) / 2) + 1) AS running_statement
FROM sys.dm_exec_requests AS r
JOIN sys.dm_exec_sessions AS s ON s.session_id = r.session_id
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS t
WHERE r.blocking_session_id <> 0
  AND r.wait_time >= {{min_wait_ms}}
  AND DB_NAME(r.database_id) = '{{db}}'
ORDER BY r.wait_time DESC;

#### 2. Head blockers (blocking others but not blocked)

**Body:** SELECT DISTINCT
    s.session_id AS head_blocker_session,
    s.login_name,
    s.host_name,
    s.program_name,
    s.status,
    s.last_request_start_time,
    s.last_request_end_time,
    c.most_recent_sql_handle
FROM sys.dm_exec_sessions AS s
JOIN sys.dm_exec_connections AS c ON c.session_id = s.session_id
WHERE s.session_id IN (
        SELECT blocking_session_id FROM sys.dm_exec_requests WHERE blocking_session_id <> 0)
  AND s.session_id NOT IN (
        SELECT session_id FROM sys.dm_exec_requests WHERE blocking_session_id <> 0);

-- Last statement the head blocker ran:
-- SELECT text FROM sys.dm_exec_sql_text(<most_recent_sql_handle>);

#### 3. Sessions sitting on an open transaction

**Body:** SELECT
    st.session_id,
    s.login_name,
    s.host_name,
    s.program_name,
    s.status,
    s.last_request_end_time,
    DATEDIFF(MINUTE, s.last_request_end_time, GETDATE()) AS idle_minutes,
    at.transaction_begin_time
FROM sys.dm_tran_session_transactions AS st
JOIN sys.dm_exec_sessions AS s ON s.session_id = st.session_id
JOIN sys.dm_tran_active_transactions AS at ON at.transaction_id = st.transaction_id
WHERE s.status = 'sleeping'
  AND DATEDIFF(MINUTE, s.last_request_end_time, GETDATE()) >= {{idle_minutes}}
ORDER BY idle_minutes DESC;

#### 4. Recent deadlocks from the system_health session

**Body:** SELECT
    xed.value('@timestamp', 'datetime2') AS deadlock_time_utc,
    xed.query('.') AS deadlock_graph
FROM (
    SELECT CAST(target_data AS xml) AS target_xml
    FROM sys.dm_xe_session_targets AS xt
    JOIN sys.dm_xe_sessions AS xs ON xs.address = xt.event_session_address
    WHERE xs.name = 'system_health'
      AND xt.target_name = 'ring_buffer'
) AS d
CROSS APPLY target_xml.nodes('RingBufferTarget/event[@name="xml_deadlock_report"]') AS q(xed)
WHERE xed.value('@timestamp', 'datetime2') >= DATEADD(HOUR, -{{hours_back}}, SYSUTCDATETIME())
ORDER BY deadlock_time_utc DESC;

**Notes:** Requires VIEW SERVER STATE. Run these read-only queries first, and do not KILL a session until you know which ERP user or job owns it and whether it is mid-transaction, since rollback can take as long as the work already done. The system_health ring buffer holds limited history, so older deadlocks may need the event files.

## Content

**Intro:** When an ERP screen hangs, someone has to find which session is holding the lock. This generator gives you four read-only T-SQL diagnostics: blocked sessions, head blockers, idle open transactions and recent deadlock graphs.

**Sections:**

#### How to use the scripts

**Body:** Enter the database name, the minimum wait to display and the idle threshold. Run the first query to see blocked requests and their statements, then the second to reach the root of the chain, because the head blocker is usually the session worth acting on. Query three finds sessions that finished their work but never committed, which is a classic cause of ERP freezes when a client crashes or a user leaves a dialog open. Query four lists deadlocks, so you can see the two statements involved.

#### Reading a blocking chain

**Body:** A blocking chain forms when session A holds a lock, B waits for A, and C waits for B. Only the head, A, is not waiting. If A is sleeping with an open transaction, the culprit is application behaviour: a screen holding a transaction open. If A is running, a long statement is the cause and you should look at its plan. Host and program name in the output tell you whether it is an ERP client, a service account job, a report tool or an integration.

#### Reading a deadlock graph

**Body:** A deadlock graph lists victims, processes, the statements they ran and the resources, typically keys or pages. SQL Server picks a victim and rolls it back, so users see an error. Look for two processes updating the same tables in opposite orders, or a missing index causing scans that lock far more rows than needed. The fix is usually consistent update order, a covering index, or shorter transactions, not more hardware.

#### Common mistakes

**Body:** Killing the first session you see, killing a session mid-rollback and expecting instant relief, and running these on a replica where the blocking is not happening are common errors. Another is trusting the program name for ERP users when connections go through a pooled service account. Combine the output with the ERP's own user session list to name the person or job.

**Cta:** If blocking recurs, we can help trace it back to the ERP screen, job or customization that causes it and propose a tested fix.

**Faq:**

#### What permission do these queries need?

**Answer:** VIEW SERVER STATE on the instance, or VIEW SERVER PERFORMANCE STATE in SQL Server 2022 and later. They only read dynamic management views, so they are safe to run in production.

#### Is it safe to kill the head blocker?

**Answer:** Only after checking what it is doing. Killing forces a rollback that can run as long as the work already done, and can leave an ERP process half complete. Contact the owner first where possible.

#### Why does the deadlock query return nothing?

**Answer:** The ring buffer keeps limited history and may have wrapped, or no deadlocks happened in the lookback period. Extended event files for system_health may hold older events.

#### Can blocking happen without any deadlock?

**Answer:** Yes, and it is far more common. Blocking is one session waiting for another. A deadlock is a cycle where each waits for the other, which SQL Server must break by killing one.

## Search terms

sql server find blocking session, head blocker query, sys.dm_exec_requests blocking_session_id, sql server deadlock graph system_health, open transaction idle session sql server, erp screen hanging sql server, kill blocking spid, sql server blocked process script

---
Talk to an engineer: https://netray.co/contact?intent=erp_ai | Machine catalog: https://netray.co/agent/catalog.json