Are my Linked Servers Being Used?

Comments: No Comments
Published on: March 5, 2012

This is a follow up to an article published on 3/1/2012.  That article showed how to find what linked servers were created on your instance of SQL Server.  You can read it here.

This article came about due to a request to find if any stored procedures are using any of the linked servers.  In addition to finding if any stored procedures may be using a linked server was the need to find the name of that procedure.  The request evolved to also include finding any SQL Agent jobs that may be using the linked server.

In response to that request, I had the idea to adapt a query I had recently written.  I will be posting that in the near future.

Query

[codesyntax lang="tsql"]

[/codesyntax]

Don’t blast me just yet due to the use of a loop (cursor).  In a case such as what has just been presented, a cursor is a legitimate tool.  I need to find all stored procedures that contain the text I specify.  In this case, I am searching for each of the linked servers.

In addition to searching all of the stored procedures for the use of a defined linked server, I am re-opening the same cursor to search all of the jobs defined on the server.  I do this in the event that the job was created with an ad-hoc query in lieu of using a stored procedure.

If you run the query, you will find that it should provide a quicker turnaround time on documenting the use of the linked servers than manually searching.

No Comments - Leave a comment

Leave a comment

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code class="" title="" data-url=""> <del datetime=""> <em> <i> <q cite=""> <strike> <strong> <pre class="" title="" data-url=""> <span class="" title="" data-url="">






Calendar
March 2012
M T W T F S S
« Feb   Apr »
 1234
567891011
12131415161718
19202122232425
262728293031  
Content
SQLHelp

SQLHelp


Welcome , today is Tuesday, September 2, 2014