Menu
Under SQL Server, is there an easy way to filter the output of sp_who2? Say I wanted to just show rows for a certain database, for example.
576
sp_Who2 is similar to sp_Who but it is not documented nor supported but it returns more information and performance counter from the current processes such as the program name executing the command, Disk IO, CPU Time, last batch execution time.
What is sp_who2 ? It's a stored procedure which is installed with SQL Server which when run, outputs a row for each “SPID”. SPID stands for Server Process ID and one is created every time an application needs to open a connection to SQL Server.
The result set of sp_who2 will contains a column named BlkBy, this represents the SPID that is currently stopping the SPID in the row. Sometimes many rows will show SPID numbers in the BlkBy column. This is because there is a chain of blockers.
You could try something like
DECLARE @Table TABLE( SPID INT, Status VARCHAR(MAX), LOGIN VARCHAR(MAX), HostName VARCHAR(MAX), BlkBy VARCHAR(MAX), DBName VARCHAR(MAX), Command VARCHAR(MAX), CPUTime INT, DiskIO INT, LastBatch VARCHAR(MAX), ProgramName VARCHAR(MAX), SPID_1 INT, REQUESTID INT ) INSERT INTO @Table EXEC sp_who2 SELECT * FROM @Table WHERE .... And filter on what you require.
54
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
