Get the Command Text for a SQL Query Based on its SPID

A lot of SQL troubleshooting starts like this:

sp_who2

The “spootoo” procedure brings back a list of SPIDs and includes information on which SPIDs are blocking. You can quickly track back to a problem SPID by following the information in the “BlkBy” column, which shows the SPID blocking a row.

Once you have the SPID, you probably want to know what query is executing, in order to work out why things are blocked. You can do that by passing the SPID into the following query:

DBCC INPUTBUFFER(50)

This is a step I often recommend people add to their troubleshooting process (rather than running a KILL against the SPID, which can be a little rash!)