List sessions / active connections in Azure SQL Database

Rene Castro - Dataedo Team Rene Castro 2018-12-10

Table of Contents:


    There are two useful procedures in debugging session problems.

    Before you begin

    Please note that you need the VIEW SERVER STATE permission to view all sessions. Otherwise, you will only see yours.

    Official command

    Query

    exec sp_who
    

    Columns

    • spid - unique session ID
    • ecid - execution context ID of a given thread associated with a specific session ID
    • status - process status
    • loginname - login name associated with the session. You can use it to identify an application user
    • hostname - host name associated with the session. You can use it to identify an application user
    • blk - session ID of the blocking process (spid is blocked by blk)
    • dbname - database used by the process
    • cmd - Database engine command executing for the process
    • request_id - ID for requests running in a specific session

    Sample results

    You can see a session list on our test server. Note that user 113 is blocked by user 93.

    More verbose command

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

    Query

    exec sp_who2
    

    Columns

    All columns from sp_who, plus:

    • CPUTime - Processing CPU time
    • DiskIO - Disk input/output time
    • ProgramName - application associated with the session. Many applications set this useful value
    • LastBatch - last activity associated with the session

    Sample results

    You can see a session list on our test server. Note that user 113 is blocked by user 93.

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

    By continuing to use our website, you consent to the use of cookies in accordance with our Privacy Policy.
    Accept