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



Related Articles :

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

Software Reviews | Book Reviews | FAQs | Tips | Articles | Performance Tuning | Audit | BI | Clustering | Developer | Reporting | DBA | ASP.NET Ado | Views tips | | Developer FAQs | Replication Tips | OS Tips | Misc Tips | Index Tuning Tips | Hints Tips | High Availability Tips | Hardware Tips | ETL Tips | Components Tips | Configuration Tips | App Dev Tips | OLAP Tips | Admin Tips | Software Reviews | Error | Clustering FAQs | Performance Tuning FAQs | DBA FAQs |