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.
- 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
You can see session list on our test server. Note that user 68 is blocked by 70.
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.
- 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
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.