Finding Connections for A Single 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
]]>

Leave a comment

Your email address will not be published.