Finding Connections for A Single Database

During trouble-shooting you may want to find all the connections relevant to one database.

You can use sp_who2 but the problem with that is it can return a lot of results for all connections and you need to scroll through entire result set.

The following script will return connections relevant to one database with other relevant information.

SET NOCOUNT ON
DECLARE @DBName VARCHAR(50) = 'AdventureWorks'
CREATE TABLE #running_databases
(SPID int,
Status varchar(50),
Login varchar(50),
HostName varchar(50),
BlkBy varchar(50),
DBName varchar(50),
Command varchar(50),
CPUTime int,
DiskIO int,
LastBatch varchar(50),
ProgramName varchar(50),
SPID2 int,
RequestID int)

INSERT INTO #running_databases
(
SPID ,
Status,
Login ,
HostName,
BlkBy,
DBName,
Command,
CPUTime,
DiskIO,
LastBatch,
ProgramName,
SPID2,
RequestID
)
EXEC sp_who2

DELETE FROM #running_databases
WHERE DBName != @DBName OR DBName is NULL

ALTER TABLE #running_databases

ADD IsProcessed int

SELECT *
FROM #running_databases

DROP TABLE #running_databases



Array

No comments yet... Be the first to leave a reply!