How to get detailed list of connections to database in sql server 2005?
There is also who is active?:
Who is Active? is a comprehensive server activity stored procedure based on the SQL Server 2005 and 2008 dynamic management views (DMVs). Think of it as sp_who2 on a hefty dose of anabolic steroids
Use the system stored procedure sp_who2
.
As @Hutch pointed out, one of the major limitations of sp_who2
is that it does not take any parameters so you cannot sort or filter it by default. You can save the results into a temp table, but then the you have to declare all the types ahead of time (and remember to DROP TABLE
).
Instead, you can just go directly to the source on master.dbo.sysprocesses
I've constructed this to output almost exactly the same thing that sp_who2
generates, except that you can easily add ORDER BY
and WHERE
clauses to get meaningful output.
SELECT spid,
sp.[status],
loginame [Login],
hostname,
blocked BlkBy,
sd.name DBName,
cmd Command,
cpu CPUTime,
physical_io DiskIO,
last_batch LastBatch,
[program_name] ProgramName
FROM master.dbo.sysprocesses sp
JOIN master.dbo.sysdatabases sd ON sp.dbid = sd.dbid
ORDER BY spid
sp_who2 will actually provide a list of connections for the database server, not a database. To view connections for a single database (YourDatabaseName in this example), you can use
DECLARE @AllConnections 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 @AllConnections EXEC sp_who2
SELECT * FROM @AllConnections WHERE DBName = 'YourDatabaseName'
(Adapted from SQL Server: Filter output of sp_who2.)