Monday, June 12, 2017

Find the stored procedures and agents that use Linked Server

Today I had a scenario where I had to identify all the stored procedures and agents that used a particular Linked Server.  We are thinking of getting rid of these multiple  Linked Servers and combining them into one server.

So I have used the sys.sql_modules table to find out what stored procedures are using these linked servers.

The code I have written is as follows.

SELECT OBJECT_NAME(object_id) object ,  *
      FROM sys.sql_modules
      WHERE
Definition LIKE '%linkedsrv1%'
 OR
Definition Like '%linkedsrv2%'
 OR
 Definition Like '%linkedsrv3%'
      AND OBJECTPROPERTY(object_id, 'IsProcedure') = 1 ;


I have also used the sysjobsteps and sysjobs tables from msdb database to find out what jobs are using these Linked Servers,

The code I have written is as follows:

  SELECT j.name AS JobName,js.command
      FROM msdb.dbo.sysjobsteps jsteps
         INNER JOIN msdb.dbo.sysjobs jobs
            ON jobs.job_id = jsteps.job_id
      WHERE
 jsteps.command LIKE   '%linkedsrv1%'
 OR
jssteps.command Like  '%linkedsrv2%'
 OR
 jsteps.command Like  '%linkedsrv3%'


Next question that I am pondering is if we consolidate these servers into one server, what are the considerations I need to be aware of.

I will be covering this in a future post.  Meanwhile appreciate some feedback from the readers.

No comments:

Step By Step Guide to resolve SQL server connection issues when connecting from R Studio

Yesterday I tried using a couple of  RevolveScaleR functions -- RxSqlServerData and RxImport.in Rstudio.  These are my learnings as part of ...