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.
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. It only takes a minute to sign up.Sign up to join this community
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.
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 (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, last_execution_time FROM sys.dm_exec_query_stats AS s1 CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS s2 WHERE s2.text like '%LinkedServerName%' and last_execution_time > convert(datetime, '2011-01-01 00:00:00.000', 121) ORDER BY s1.sql_handle, s1.statement_start_offset, s1.statement_end_offset
Lots of caveats for this:
Note: If you do tweak it and get it working, please change the above sql
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.