Sql-server – How to determine the spid of the session that is making a query on a linked server

sql serversql-server-2008

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?

Best Answer

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%';
Related Question