How to find and kill a blocking process in SQL?

Use sys.dm_exec_requests and sp_who2 to identify the blocking process by checking blocking_session_id and the BlkBy column; 2. Query sys.dm_exec_sessions with sys.dm_exec_sql_text to get details like login name and SQL text of the blocking session; 3. Execute KILL <blocking_session_id> to terminate the blocking process, or use KILL <blocking_session_id> WITH STATUSONLY to monitor rollback progress; 4. Prevent future issues by improving indexing, keeping transactions short, using appropriate isolation levels like READ COMMITTED SNAPSHOT, and monitoring for long-running queries. This approach resolves current blocking and reduces future occurrences.

When a SQL query is blocked by another process, it can slow down or freeze your application. Finding and killing the blocking process is a common troubleshooting task in SQL Server. Here’s how to do it step by step.

1. Identify the Blocking Process

Use system views like sys.dm_exec_requests and sys.dm_exec_sessions to find which session is causing the block.

SELECT 
    r.session_id,
    r.blocking_session_id,
    r.status,
    r.command,
    r.wait_type,
    r.wait_time,
    r.wait_resource,
    t.text AS sql_text
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t
WHERE r.blocking_session_id <> 0;

This query shows:

• session_id: The ID of the blocked process.
• blocking_session_id: The ID of the process causing the block.
• sql_text: The actual SQL command being executed.

You can also check all active sessions, including the blocker:

EXEC sp_who2;

Look for entries where the BlkBy column is not empty — those are blocked sessions, and the ID in BlkBy is the blocking SPID.

2. Get Details About the Blocking Session

Once you have the blocking_session_id, get more info about it:

SELECT 
    s.session_id,
    s.login_name,
    s.host_name,
    s.program_name,
    t.text AS current_sql
FROM sys.dm_exec_sessions s
CROSS APPLY sys.dm_exec_sql_text(s.session_id)
WHERE s.session_id = <blocking_session_id>;

Replace <blocking_session_id> with the actual ID. This helps you understand who or what is causing the block — maybe a long-running report or an open transaction.

3. Kill the Blocking Process

If the process is not critical and has been running too long, you can terminate it using the KILL command:

KILL <blocking_session_id>;

For example:

KILL 54;

⚠️ Warning: Killing a session rolls back any uncommitted transactions, which might take time and temporarily increase load.

If you need more info after killing, you can use:

KILL <blocking_session_id> WITH STATUSONLY;

This shows the rollback progress.

4. Prevent Future Blocking Issues

While killing a process resolves the immediate issue, consider:

• Indexing: Poorly indexed queries can hold locks longer.
• Keep transactions short: Avoid long-running transactions.
• Use appropriate isolation levels: Consider READ COMMITTED SNAPSHOT to reduce blocking.
• Monitor regularly: Set up alerts for long-running or blocking queries.

Basically, find the blocker using DMVs, confirm it’s safe to kill, then use KILL to stop it. It’s a quick fix, but addressing the root cause prevents recurrence.

The above is the detailed content of How to find and kill a blocking process in SQL?. For more information, please follow other related articles on the PHP Chinese website!