HiveBrain v1.2.0
Get Started
← Back to all entries
patternsqlMinor

SQL Server linked server's DATA ACCESS property changes to false

Submitted by: @import:stackexchange-dba··
0
Viewed 0 times
sqldatafalsepropertychangesserverlinkedaccess

Problem

I have a SQL Server instance that includes an agent job that accesses a second instance via a linked server. Occasionally, and seemingly randomly, the DATA ACCESS property of the linked server changes from true to false (and the agent job begins to fail).

That agent job runs every 10 minutes between 5am and 8pm, and the change to the linked server always happens sometime overnight, though I haven't yet been able to determine when exactly. I do have other jobs--some vendor supplied, and others such as backups, index and statistics maintenance, etc. (Ola Hallengren's scripts)--but nothing else that makes use of that linked server, and nothing that would explicitly touch it.

What would cause linked server DATA ACCESS to change? What troubleshooting or tracing can I do to determine what is causing this?

Solution

The only way to change the linked server properties I know of is via system procedure sp_serveroption.

This procedure is called in the background even if you change the properties via GUI.

So you can create an Extended Event session to track the execution of this proc.
CREATE EVENT SESSION [LinkedServerChange] ON SERVER
ADD EVENT sqlserver.module_start
(
SET collect_statement=(1)
ACTION
(
sqlserver.client_app_name
,sqlserver.client_hostname
,sqlserver.database_name
,sqlserver.server_principal_name
,sqlserver.tsql_stack
)
WHERE [object_name]=N'sp_serveroption'
)
ADD TARGET package0.event_file
(
SET filename=N'LinkedServerChange'
, max_file_size=(2)
, max_rollover_files=(2)
)
GO
ALTER EVENT SESSION [LinkedServerChange] ON SERVER STATE = START


The result looks like this

Context

StackExchange Database Administrators Q#327492, answer score: 9

Revisions (0)

No revisions yet.