Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

We have a tonne of linked servers on our database server that we would like to clean up.

Is it possible to determine the last date a linked server was used? e.g. the last time a query was performed through it.

share|improve this question
up vote 5 down vote accepted

You'll have to setup SQL Profiler to monitor for queries which use the linked server. There's no DMV that you can easily monitor for usage.

share|improve this answer

You might be able to look at the dmv sys.dm_exec_sql_text to look at where the linked server name was last used in a sql statement.

set transaction isolation level read uncommitted

    (SELECT TOP 1 SUBSTRING(s2.text,statement_start_offset / 2+1 , 
      ( (CASE WHEN statement_end_offset = -1 
         THEN (LEN(CONVERT(nvarchar(max),s2.text)) * 2) 
         ELSE statement_end_offset END)  - statement_start_offset) / 2+1)) 
        AS sql_statement,
FROM sys.dm_exec_query_stats AS s1 
    CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS s2  
    s2.text like '%LinkedServerName%' 
    and last_execution_time > convert(datetime, '2011-01-01 00:00:00.000', 121)
    s1.sql_handle, s1.statement_start_offset, s1.statement_end_offset

Lots of caveats for this:

  • if linked server is used via view/function it may not appear in your result set
  • it will only include any sql that is in the plan cache
    • the plan cache is cleared on restart
    • SQL Server will clear out old plans from the cache once it's size limits are reached

Note: If you do tweak it and get it working, please change the above sql

share|improve this answer
That will return for the queries which are currently in the plan cache, not every query run since the SQL Instance was restarted. Depending on how large your plan cache is that could be a very small sub set of your queries. – mrdenny Sep 8 '11 at 19:41
This is a great creative answer and for our environment it will give us a general idea of our linked server usage (most uses will be in plain SQL rather than views/functions). Appreciate the caveats you guys mentioned and we will still need to profile to be absolutely sure that they're not being used. – Danere Sep 8 '11 at 23:21

The only way you can be sure one is not being used is to drop it. ;) I have monitored linked servers for weeks and then had someone complain within hours of dropping it.

You should script it out first. Right click in Object Explorer and chose the appropriate option.

share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.