Query timeout Linkedserver is not working

Question

hi,

i have an issue, i've configued 3 linked server each one is use to execute a store procedure on another sql server instance, i've configured the query timeout in 10 seconds but sometimes the query stops after 15 seconds. Once the quey was run for more than 45 seconds i have no idea what is going on.

the enviroments are SQL Server 2014 with 3 linked server to connect to a SQL Server 2014 the last one has the procedures to run

Answer 1

You would need to trace on the remote servers to see what is happening.

When a timeout elapses, the client (which is in this case is the originating server) sends an attention signal to the server to cancel the operation. The red button in SSMS does the same thing. There is a trace event for attention signals in the Exception category, by the way.

When SQL Server gets the attention signal it will always roll back the current statement. It will also roll back any open transaction if the setting XACT_ABORT is ON. The rollback may take some time.

Furthermore, due to the nature of the SQL Server operating system, the process may not notice the timeout. It can only notice the time, if it voluntarily yields. Which it can't do if it is making a call to the OS or something else outside SQL Server, for instance accessing yet a linked server.

Answer 2

Hi @Camilo Ponce Catalan ，

Set the query timeout to 0 to have a test.

There are many factors and causes that can lead this issue, you can check the error log to see whether there are related error records.
By default, the error log is located at Program Files\Microsoft SQL Server\MSSQL.n\MSSQL\LOG\ERRORLOG and ERRORLOG.n files.

Then follow steps to have a test:

1.Turn off your firewall and antivirus softwares to have a test;
2.Enable TCP\IP protocol both in your server side and client side and use port:1433 in SSCM;
3.Ping the linked server's ip and PC name;
4.Try code as next to configure the timeout is 0, and then begin your test:

USE  DBNAME ;   
GO   

EXEC sp_configure 'remote query timeout', 0 ;   
GO   

RECONFIGURE ;   
GO   

5.If there is one timeout, try to ping each other's ip and PC name to verify again and then check your error log.

Note: After you do changes in your SSCM, please remember to restart your SQL Service.

BR,
Mia

---

If the answer is helpful, please click "Accept Answer" and upvote it.

Answer 3

Hi @Camilo Ponce Catalan ，

Is the reply helpful?

BR,
Mia

---

If the answer is helpful, please click "Accept Answer" and upvote it.

Answer 4

Hi @Camilo Ponce Catalan ，

i'm confused about this, now i'm looking for a bottleneck or blocking session.

If you are looking for the a bottleneck or blocking session, code as next will be helpful

SELECT s.session_id, s.status, s.login_time, s.host_name,  
        s.program_name, s.host_process_id, s.client_version,   
        s.client_interface_name, s.login_name, s.last_request_start_time,   
        s.last_request_end_time, c.connect_time, c.net_transport,   
        c.net_packet_size, c.client_net_address, r.request_id, r.start_time,   
        r.status, r.command, r.database_id, r.user_id, r.blocking_session_id,   
        r.wait_type, r.wait_time, r.last_wait_type, r.wait_resource,   
        r.open_transaction_count, r.transaction_id, r.percent_complete,   
        r.cpu_time, r.reads, r.writes, r.granted_query_memory   
    FROM sys.dm_exec_requests r   
        RIGHT OUTER JOIN sys.dm_exec_sessions s  
        ON r.session_id = s.session_id  
        RIGHT OUTER JOIN sys.dm_exec_connections c  
        ON s.session_id = c.session_id  
    WHERE  s.is_user_process = 1  

And there is also a dynamic management view sys.dm_os_wait_stats, which can return the total number of waits and wait time for all wait states since SQL Server was started. From these accumulated values, we can see which type of wait SQL Server often encounters.

BR,
Mia

---

If the answer is helpful, please click "Accept Answer" and upvote it.

Answer 5

For the record both instances are SQL Server 2014 SP2 no CU installed