Contents
How to check if MS SQL Server, linked server object is in use?
Also do a search on any source code for applications that may be developed against that database. 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.
How to check the status of a database?
Example 1 – sys.databases catalog view ColumnName Value is_auto_shrink_on 0 state 0 state_desc ONLINE is_in_standby 0
How can I check if my linked server is still in use?
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.
How can I find dependencies on a linked server?
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 sometime over the next year. The person ranting on the other end will help you identify what it was used for.
Can a linked server make a procedure call?
By default, Linked Servers are not able to make stored procedure calls. This is easily remedied by enabling a single setting on the Linked Server. Browse to the Linked Server properties, choose Server Options and the 4 th option in the list will be RPC Out. Simply change that value to True and click OK.
How can I find out what is using a linked server?
This is useful when you are trying to find out what is using a linked server. So you get the name of the linked server (and it’s host name) and run a search on that. It tells you a handful of objects. You then run it again, but this time looking for those objects (say, in other stored procedures or in SSIS/Jobs).
How to configure a linked server in SQL Server?
You can configure a linked server by using SQL Server Management Studio or by using the sp_addlinkedserver (Transact-SQL) statement. OLE DB providers vary greatly in the type and number of parameters required.