How do I determine the spid of the session that is making a query on a linked server?

I have a session running on a SQL Server instance that is blocking other sessions (holding on to a lock without releasing it).

Querying sys.sysprocesses yielded the hostname of a different SQL Server instance.

I want to know that spid of the originating session that is ultimately responsible for blocking the sessions on the linked server.

• How would I do that?

You would have to check running queries on the remote server. You could isolate linked server queries by filtering on your linked server name with the following dmv query.

SELECT [s].[session_id] AS                          [spid]
       ,[s].[status]
       ,[s].[login_name] AS                         [loginName]
       ,[s].host_name AS                            [hostName]
       ,COALESCE(DB_NAME([r].[database_id]), '') AS [dbName]
       ,[r].[command]
       ,[wt].[wait_type] AS                         [waitType]
       ,[s].[login_time] AS                         [loginTime]
       ,[s].[last_request_end_time] AS              [lastBatch]
       ,[s].[program_name] AS                       [programName]
       ,[t].text AS                                 [lastSQLText]
FROM [sys].[dm_exec_sessions] AS [s]
     LEFT JOIN [sys].[dm_exec_requests] AS [r]
     ON [r].[session_id] = [s].[session_id]
     LEFT JOIN [sys].[dm_exec_connections] AS [c]
     ON [c].[session_id] = [s].[session_id]
     LEFT OUTER JOIN [sys].[dm_os_waiting_tasks] AS [wt]
     ON [s].[session_id] = [wt].[session_id]
     CROSS APPLY [sys].[dm_exec_sql_text]([c].[most_recent_sql_handle])AS [t]
WHERE [s].[status] = 'Running'
  AND [t].[text] like '%LinkedServerName%';

StackExchange Database Administrators Q#86441, answer score: 2