Sql-server – Linked Server Timeout – Not Queried

jobslinked-serversql serverstored-procedures

I have a job that runs for multiple regions across the globe. Depending on what region is running and what time zone it's in, the job might not run. It uses GOTO to get to the end and reports that it was skipped due to time and completes.

This runs as expected with no issues.

The Issue: there are times when a linked server farther down the proc is under really heavy load. We know about it and cannot avoid it – so we don't query the linked server at this time.

But, we're still getting an error as if it's queried:

Cannot obtain the schema rowset "DBSCHEMA_TABLES_INFO" for OLE DB
provider "SQLNCLI11" for linked server returned message "Unspecified
error". [SQLSTATE 01000] (Error 7412) OLE DB provider "SQLNCLI11" for
linked server returned message "Query timeout expired". [SQLSTATE
01000] (Error 7412). The step failed.

Are linked servers validated when a proc starts causing this schema check that times out?

Best Answer

This is a guess, but I would try enabling lazy schema validation for the linked server under heavy load:

EXEC master.dbo.sp_serveroption @server=N'Linked_Server_name', @optname=N'lazy schema validation', @optvalue=N'true'

If this option is set to false, the default value, SQL Server checks for schema changes that have occurred since compilation in remote tables. This check occurs before query execution. If there is a change in the schema, SQL Server recompiles the query with the new schema.

If this option is set to true, schema checking of remote tables is delayed until execution. This can cause a distributed query to fail with an error if the schema of a remote table has changed between query compilation and execution.

Source

As to only check the linked server metadata when querying the linked server itself.

Another workaround could be using dynamic sql statements to query the linked server

DECLARE @SQL nvarchar(max)
IF (Linked Server not under heavy load = true)
BEGIN
SET @SQL = 
N'SELECT * FROM OPENQUERY(LinkedServer,''SELECT * FROM master.sys.databases'')'
EXEC SP_EXECUTESQL @SQL
END
Related Question