How to get detailed list of connections to databas

Question

How to get detailed list of connections to database in sql server 2005?

Answer 1

Use the system stored procedure sp_who2.

Answer 2

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.)

Answer 3

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 

Answer 4

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