Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Find SPID of Linked Server Call Expand / Collapse
Author
Message
Posted Tuesday, March 12, 2013 2:14 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: 2 days ago @ 2:49 AM
Points: 192, Visits: 429
Hi SCC,

I have a stored procedure on server A that makes a linked server call to server B.... on server A I see the wait type is OLEDB so I want to look at the SPID on server B... is there a way to identify the remote SPID other than connecting to the server and doing an sp_who2 and looking for the client who made the connection and then username of the linked server?

Thanks!
Post #1430088
Posted Tuesday, March 12, 2013 4:56 PM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Thursday, December 11, 2014 1:06 PM
Points: 4,253, Visits: 4,295
If you want to identify the SPID on the remote Server you need to connect to the Remote Server.

I often use sp_who3 and sp_WhoIsActive. They will only show active SPIDS.


For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Post #1430143
Posted Thursday, March 28, 2013 7:44 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: 2 days ago @ 2:49 AM
Points: 192, Visits: 429
I actually found that you can find the SPID of the of a linked server call using master..sysprocesses on the local server under the waitresource field, INVENTORY is the alias to the linked server in this case and SPID 139 is remote SPID:

spid kpid blocked waittype waittime lastwaittype waitresource
53 8428 0 0x006D 78 OLEDB INVENTORY (SPID=139)
Post #1436468
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse