How to check if MS SQL Server, Linked Server object is in use

Linked server objects allow one SQL server to connect to another at the database engine level. A connection is defined when creating the linked server, including the credentials that should be used for that connection.

As time passes, the challenge is knowing if that old linked server object is still being utilised by something, a report or script or import somewhere. The knowledge of the reason for its existence and person responsible may be long gone.

Lazy & bad approach
The quickest (not easiest) method to find dependencies on a linked server is to delete the linked server and wait for the phone to ring at sometime over the next year. The person ranting on the other end will help you identify what that linked server was used for. If the linked server was involved in a complex integration orchestration, or year/month end reporting, then the fallout from this maverick approach may not be pleasant to resolve, nor may the call be in social hours...

Instead carefully identify, then remove or correctly document the dependencies on the linked server object.

Here a few tips to help you find those dependencies:

Look for dependencies using SQL inbuilt dependency tracking

Using script from here List All Objects Using Linked Server to investigate any objects it returns.

SELECT   
    Distinct   
    referenced\_Server\_name As LinkedServerName,  
    referenced\_schema\_name AS LinkedServerSchema,  
    referenced\_database\_name AS LinkedServerDB,  
    referenced\_entity\_name As LinkedServerTable,  
    OBJECT\_NAME (referencing\_id) AS ObjectUsingLinkedServer  
FROM sys.sql\_expression\_dependencies  
WHERE referenced\_database\_name IS NOT NULL  
And referenced\_Server\_name = 'Enter LinkedServerName here'

Script out objects

In built dependency checking does not work if the dependency is “hidden” in a dynamically generated (sp_executesql) SQL statement or SQL jobs or is used by SQL replication subscriptions etc. If you don’t trust the dependency script above or have older SQL server version, script out all your stored procedures, views, user defined functions, SQL jobs and any other SQL objects that might reference the linked server. Then perform a text search over all the resulting scripts for the linked server name. If you find the text, in turn identify if that object is still in use. If you use notepad++ or similar advanced text editor, they provide multiple file search with regular expression support. The regular expression support is handy if the linked server name is used in other parts of the database, allowing the search to be narrowed down to text patterns likely to be a linked server.  Also do a search on any source code for applications that may be developed against that database.

Use Sql Profiler

Run SQL server Profiler for a month against the target server (on a spare machine) to see if the linked server login name appears, this is the login name of the connection set up in the linked server configuration. I recommend a month as month end scripts or monthly maintenance scripts will be caught, however this won’t catch scripts or reports that are only ran at year end or once in a while. The trick here is to make certain you change the linked server connection to use a uniquely identifiable login name, that way it is possible to use the filter option in SQL profiler to only log events from that LoginName. Changing the login name has to be a considered move as it will change the security context and may thus affect rights to the destination server objects. These approaches all have down sides, but on less complex scenarios this technique works well.

Before deleting the linked server object

In SSMS, right click the object and script out the linked server. Put the resulting create script somewhere easy to find before deleting it, this way it will be quick to put it back again, should an urgent need for it occur, say at year end finance closing, months later. Having the create script helps get things running and buys you time to fix the offending referencing scripts, or document them correctly if it is decided to retain the linked server after all.