June 19, 2014 at 3:26 am
Hi,
I need to see if people/jobs on a database are making calls to remote servers.
I was trying "sys.dm_exec_query_stats CROSS APPLY sys.dm_exec_sql_text(d.plan_handle)" on a server for testing but that isn't working (I have jobs collecting data remotely every 15 minutes but a "text like" isn't even picking up the procedure calls).
I was wondering if there was any view that counts each time a linked server is called since startup? That way I could say this particular server doesn't access remote DBs without running a trace. If this view said it does go across links then I'd need to run a trace to find out where but it would be better if there's a view first?
June 22, 2014 at 7:45 am
I cannot offer a solution but maybe another way to get the information you want.
For procedures, views etc. have a look at sys.sql_modules.definition, you find the DDL code there.
For SQL-Jobs have at msdb.dbo.sysjobsteps.command, you find the statement of the job steps there.
Use the found SQL-codes and a substring search to find use of linked servers. Which servers are available you can find out using sp_helpserver.
This way you won't get the number or the last appearance of calls to linked servers, but at least you get the objects that use a linked server if called.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply