SQL Serverous

Identify the T-SQL being run by a SPID

This is one I used to use a lot, and had cause to use it this morning. An ETL process to truncate a staging table was being blocked by another process on the server, and I needed to identify exactly what command the blocking process was attempting to execute. I found the SPID by executing sp_who2, which showed me the SPID that was blocking the truncate command (in this case SPID 54). I then executed the following command to find out exactly what command was causing the block to happen:

DBCC inputbuffer(54)

The result set returned shows the actual T-SQL that was last executed for that SPID, which was a very large insert from a remote PostgreSQL database.

This is a quick and useful method for tracking down issues and investigating what is happening on your server.

Filed under // tsql

Comments (0)

Quick tip: Search for a term in a Stored Procedure

Need to find out if a term is referenced in any stored procedures in your database? Use the following T-SQL statement...

select name, definition
from sys.procedures p
    inner join sys.sql_modules m
        on p.object_id = m.object_id
where
    m.definition like '%search term%';

This will work in SQL Server 2005 and 2008.

SQL Serverous

tsql

Identify the T-SQL being run by a SPID

Quick tip: Search for a term in a Stored Procedure

My Other Sites

Tags

Get Updates