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
Definition LIKE '%linkedsrv1%'
Definition Like '%linkedsrv2%'
 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 AS JobName,js.command
      FROM msdb.dbo.sysjobsteps jsteps
         INNER JOIN msdb.dbo.sysjobs jobs
            ON jobs.job_id = jsteps.job_id
 jsteps.command LIKE   '%linkedsrv1%'
jssteps.command Like  '%linkedsrv2%'
 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:

Rerunning a SSRS subscription report

Yesterday I had a situation where  a couple of SSRS reports failed due to a network upgrade. The SSRS report was not able to access the SQ...