I have seen whenever customer complains about Query Time Out issue, there is a tendency to play around with this settings. This does not work the way you might think it does.
I would like to quote from TechNet:
https://technet.microsoft.com/en-us/library/ms177457(v=sql.105).aspx
Use the remote query timeout option to specify how long, in seconds, a remote operation can take before Microsoft SQL Server times out. The default is 600, which allows a 10-minute wait. This value applies to an outgoing connection initiated by the Database Engine as a remote query. This value has no effect on queries received by the Database Engine.
It clearly says, this applies to only outgoing connection and does not have any effect on incoming queries. The misunderstood part here is, what is outgoing and what is incoming query for SQL Server. This should get clarified once you read through this article.
Here is my LAB setup for the Demo:
As you can see in the above screen shot, I have set the remote query timeout setting for the default instance to 5 sec(s).
You can execute the below query to set ‘Remote Query Timeout’ settings:
sp_configure 'Remote Query Timeout', 5 GO RECONFIGURE GO
Kept the default settings for the Linked Server SQL Instance (SQL_Named). i.e. 600 Sec(s)
Now if I run a remote query to linked server that would be an outgoing query for the default instance and incoming for Linked Server.
In Linked server I am keeping an open transaction so that the lock on the table [AdventureWorks2014].[Production].[Product] does not get released.
Initiate a remote query from the default instance (Outgoing Query).
If you can see that the query got timed out exactly after 5 sec(s). So, it proves that the remote query time out settings applies for remote query (i.e. for outgoing query).
Now lets see how it works for incoming query. Keep and open transaction on the same table. This time executing it in the default instance.
Open a different session for the default instance and run select on the same table. Both the queries are incoming for the default instance.
This time query is still waiting even though it crossed 12 sec(s) without getting timed out. This was an incoming query for the database engine of default instance and hence Remote Query Timeout Settings (which is set to 5 sec(s)) did not apply.
I hope this clarifies how exactly “Remote Query Timeout” option works.
More over if you like video training check the demo here:
You may also like to spend couple of minutes to check the below articles:
how to delete tempdb data file without restarting SQL Server.
SQL Database Restore using NetBackup
SSRS Encryption Key Backup Information
User Defined Profiler Template Location
Remove or delete Tempdb data file without restarting SQL Server
Fact about Dedicated Administrator Connection (DAC)
