Filter results from sp_who2

It can be very helpful to filter the results of sp_who2 in SQL 2008:

DECLARE @sp_who2_temp_table TABLE(
        SPID INT,
        Status VARCHAR(100),
        LOGIN VARCHAR(150),
        HostName VARCHAR(100),
        BlkBy VARCHAR(100),
        DBName VARCHAR(100),
        Command VARCHAR(250),
        CPUTime INT,
        DiskIO INT,
        LastBatch VARCHAR(100),
        ProgramName VARCHAR(254),
        SPID_1 INT,
        REQUESTID INT
)

INSERT INTO @sp_who2_temp_table EXEC sp_who2

SELECT * FROM @sp_who2_temp_table
WHERE DBName = 'DatabaseName'

Study the blocking query:

-- Enter SPID
DBCC INPUTBUFFER(77)

 

Use sp_who2 to find any running query, or the following query to view jobs and percent complete:

SELECT R.percent_complete, T.text, R.Status, R.Command, DatabaseName = db_name(R.database_id)
, R.cpu_time, R.total_elapsed_time, R.percent_complete
FROM sys.dm_exec_requests R
CROSS APPLY sys.dm_exec_sql_text(R.sql_handle) T
ORDER BY Command

Enjoy the results!

Leave a Reply