Is it possible to see blocker's host name or machine name using sp_WhoIsActive?

I am using sp_WhoIsActive.

When blocking is occurred on a server I want to quickly identify who is the culprit.

Column login_name does not give me much info about blocker because there are hundreds people using company's app and logged in under same login_name.

I am able to see blocking_session_id but this does not tell me what is the blocker's host_name.

There is also a column host_name but this is the one who is a waiter (not blocker)

So is any way I can quickly find the host_name, machine name or any other identity of the user who is the blocker?

Yes, you simply have to correlate the blocking_session_id to the session_id of the blocker. In the following output, you see that session 68 is blocked by session 66, so you simply have to find the data for session 66.

To find block leaders most quickly, be sure to use the following parameters so that the session blocking the most other sessions will be at the top:

@find_block_leaders = 1, @sort_order = '[blocked_session_count] DESC'

Sample output will be as follows:

sp_whoisactive 
      @output_column_list='[session_id][block%][host_name][login_name]'
    , @find_block_leaders = 1
    , @sort_order = '[blocked_session_count] DESC'

session_id    blocking_session_id    blocked_session_count    host_name      login_name
66            NULL                    1                       host1          user1
68            66                      0                       host2          user2

If all of the sessions are coming from an application server, which is quite common, then the host_name is simply going to be that application server and there is no way from the SQL Server side to see what actual client computer is associated with that connection from the application server. If you want to track something like that down you would need to start logging relevant information at the application server, or, if you can figure it out by looking at the queries, use extended events or traces to see what queries are being run in that session. No easy way about it unless the application already has the plumbing to turn on some associated logging.

See sp_whoisactive: Leader of the Block

Also of note is that the SQL client does not always set the host_name property, so if it's blank, it's simply because the application configured the SQL connection to omit that information.

StackExchange Database Administrators Q#267254, answer score: 5