List sessions / active connections in SQL Server

Michal Wrobel - Dataedo Team Michal Wrobel 2018-06-29

Table of Contents:


    There are two procedures useful in debugging session problems.

    Before you begin

    Please note that you need VIEW SERVER STATE permission to view all the sessions. Otherwise you'll only see yor own.

    Official command

    Query

    exec sp_who
    

    Columns

    • spid - unique session ID
    • status - process status
    • loginname - login name associated with the session. You can use ti to identify application user
    • hostname - host name associated with the session. You can use ti to identify application user
    • blk - session ID of the blocking process (spid is blocked by blk)
    • dbname - database used by process

    Sample results

    You can see session list on our test server. Note that user 68 is blocked by 70.

    sample results

    More verbose command

    SQL Server has also second version of this procedure - sp_who2. This procedure shows some more information that you can use to identify process.

    Query

    exec sp_who2
    

    Columns

    • all columns from sp_who, plus:
    • ProgramName - application associated with the session Many applications set this useful value
    • LastBatch - last activity associated with the session

    Sample results

    You can see session list on our test server. Note that user 68 is blocked by 70. Both users are using Microsoft SQL Server Management Studio.

    sample results

    0
    There are no comments. Click here to write the first comment.