February 5, 2015 at 1:46 am
Hi,
We have around 15 linked servers in 2 servers and application is querying the views and stored procedures using these 2 servers.
I would like to know
1. Is there a way to find from SQL(2008) Side, how many linked servers are being used and how many are not.. as application team want us to remove the unnecessary linked servers.
2. Is there any post/document which gives us more information on how to troubleshoot Linked server issues.
3. are there any DMV's or any Views where the historical information of Linked servers is maintained.
February 5, 2015 at 2:26 am
Try this
DECLARE @VName VARCHAR(256)
DECLARE Findlinked CURSOR LOCAL STATIC FORWARD_ONLY READ_ONLY
FOR
SELECT name AS name
FROM sys.servers
WHERE is_linked = 1
OPEN Findlinked;
FETCH NEXT FROM Findlinked INTO @VName;
WHILE @@FETCH_STATUS = 0
BEGIN
--Stored procedures, Views, Functions, Queues
IF EXISTS ( SELECT OBJECT_NAME(object_id) [ObjectName -SPs]
FROM sys.sql_modules
WHERE Definition LIKE '%' + @VName + '%'
AND OBJECTPROPERTY(object_id, 'IsProcedure') = 1 )
SELECT @VName [LinkedServer] ,
OBJECT_NAME(object_id) [ObjectName]
FROM sys.sql_modules
WHERE Definition LIKE '%' + @VName + '%'
AND ( OBJECTPROPERTY(object_id, 'IsProcedure') = 1
OR OBJECTPROPERTY(object_id, 'IsView') = 1
OR OBJECTPROPERTY(object_id, 'IsInlineFunction') = 1
OR OBJECTPROPERTY(object_id, 'IsQueue') = 1
);
FETCH NEXT FROM Findlinked INTO @VName;
END
CLOSE Findlinked
OPEN Findlinked;
FETCH NEXT FROM Findlinked INTO @VName;
WHILE @@FETCH_STATUS = 0
BEGIN
--Jobs
IF EXISTS ( SELECT j.name AS JobName ,
js.command
FROM msdb.dbo.sysjobsteps js
INNER JOIN msdb.dbo.sysjobs j ON j.job_id = js.job_id
WHERE js.command LIKE '%' + @VName + '%' )
SELECT @VName [LinkedServer] ,
j.name AS JobName ,
js.command
FROM msdb.dbo.sysjobsteps js
INNER JOIN msdb.dbo.sysjobs j ON j.job_id = js.job_id
WHERE js.command LIKE '%' + @VName + '%'
FETCH NEXT FROM Findlinked INTO @VName;
END
CLOSE Findlinked
DEALLOCATE Findlinked
I modified it a bit from the following source http://jasonbrimhall.info/2012/03/05/are-my-linked-servers-being-used/ by Jason Brimhall.
You can check out this link - https://msdn.microsoft.com/en-us/library/ms176105.aspx
to add something additionally to be checked for your linked servers.
Igor Micev,
My blog: www.igormicev.com
February 5, 2015 at 5:51 am
similar to what Igor posted, the code below can help find objects that reference a linked server, but can't find adhoc usage of a linked server, for example if an applciaitoon is calling a SELECT From LinkedServer...TableName
SELECT
SCHEMA_NAME(so.SCHEMA_ID) AS SchemaName,
so.name AS ObjectName,
so.type_desc,
sed.referenced_server_name,
sed.referenced_database_name,
sed.referenced_schema_name,
sed.referenced_entity_name,*
FROM sys.sql_expression_dependencies sed
JOIN sys.objects so ON sed.referencing_id = so.OBJECT_ID
WHERE sed.referenced_server_name IS NOT NULL
OR sed.referenced_database_name IS NOT NULL
Lowell
February 5, 2015 at 6:09 am
Lowell (2/5/2015)
similar to what Igor posted, the code below can help find objects that reference a linked server, but can't find adhoc usage of a linked server, for example if an applciaitoon is calling a SELECT From LinkedServer...TableName
SELECT
SCHEMA_NAME(so.SCHEMA_ID) AS SchemaName,
so.name AS ObjectName,
so.type_desc,
sed.referenced_server_name,
sed.referenced_database_name,
sed.referenced_schema_name,
sed.referenced_entity_name,*
FROM sys.sql_expression_dependencies sed
JOIN sys.objects so ON sed.referencing_id = so.OBJECT_ID
WHERE sed.referenced_server_name IS NOT NULL
OR sed.referenced_database_name IS NOT NULL
Great, thanks!
Igor Micev,
My blog: www.igormicev.com
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply