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.