September 4, 2017 at 7:11 am
Hello
We have a server with sql server 2012 SP3 CU5, when we run a query directly at the server it will take some time to retrieve the data but it will finish, but when a user from a remote location tries the same the connection is killed after 10 minutes sharp, we have check the timeout settings at the server and all those are set to infinite, the same is set at the client side, so does anyone know what could be happening?
September 4, 2017 at 7:30 am
Have you set the remote timeout at the SQL server level via the server properties / sp_configure?
Is the output of the below 0 or 600?
select value_in_use from sys.configurations where name = 'remote query timeout (s)'
September 4, 2017 at 7:42 am
anthony.green - Monday, September 4, 2017 7:30 AMHave you set the remote timeout at the SQL server level via the server properties / sp_configure?Is the output of the below 0 or 600?
selectvalue_in_use from sys.configurations where name = 'remote query timeout (s)'
That returns 0
September 4, 2017 at 8:05 am
OK, all the connection strings specify "Connection Timeout=0".
Is the app running through any middle ware which might have been missed from a timeout, or anything in a global configuration which may be overriding.
September 4, 2017 at 8:21 am
yes, all the settings are in 0, the app is sql management studio, I already look at the config from it and it also has the timeout set to 0
September 5, 2017 at 11:00 am
ricardo_chicas - Monday, September 4, 2017 7:11 AMHello
We have a server with sql server 2012 SP3 CU5, when we run a query directly at the server it will take some time to retrieve the data but it will finish, but when a user from a remote location tries the same the connection is killed after 10 minutes sharp, we have check the timeout settings at the server and all those are set to infinite, the same is set at the client side, so does anyone know what could be happening?
Might need to check the network configs. That is another layer of control that may be overriding your server settings.
Network devices can be configured to shut down open ports if they are open too long, for example.
And they can have their own time out settings that control the network itself.
September 5, 2017 at 2:27 pm
Is the client connecting from the remote site over some form of VPN? Could there be an idle timeout in the VPN software?
When you that the connection is killed after 10 minutes, is it just the connection to SQL Server or to everything (like possibly the NIC going to sleep due to user inactivity)?
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
September 5, 2017 at 2:30 pm
bmg002 - Tuesday, September 5, 2017 2:27 PMIs the client connecting from the remote site over some form of VPN? Could there be an idle timeout in the VPN software?When you that the connection is killed after 10 minutes, is it just the connection to SQL Server or to everything (like possibly the NIC going to sleep due to user inactivity)?
it seems to be the query only, if they retry it connect to the database again
September 5, 2017 at 2:35 pm
Is the remote connection connecting through a link server or app server? in that case you need to check the query timeout setting in link server/ app server connection configuration.
September 5, 2017 at 2:49 pm
ricardo_chicas - Tuesday, September 5, 2017 2:30 PMbmg002 - Tuesday, September 5, 2017 2:27 PMIs the client connecting from the remote site over some form of VPN? Could there be an idle timeout in the VPN software?When you that the connection is killed after 10 minutes, is it just the connection to SQL Server or to everything (like possibly the NIC going to sleep due to user inactivity)?
it seems to be the query only, if they retry it connect to the database again
Do you have auto-close turned on?
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
September 5, 2017 at 3:00 pm
where is the auto-close option?
September 5, 2017 at 3:02 pm
bmg002 - Tuesday, September 5, 2017 2:49 PMricardo_chicas - Tuesday, September 5, 2017 2:30 PMbmg002 - Tuesday, September 5, 2017 2:27 PMIs the client connecting from the remote site over some form of VPN? Could there be an idle timeout in the VPN software?When you that the connection is killed after 10 minutes, is it just the connection to SQL Server or to everything (like possibly the NIC going to sleep due to user inactivity)?
it seems to be the query only, if they retry it connect to the database again
Do you have auto-close turned on?
It is set to false
September 5, 2017 at 3:30 pm
The database they were connected to had auto-close set to false, correct? I believe that is a database level setting... I am just thinking if they are running a query that uses the 3 part naming (<database>.<schema>.<object>), they could be running the query from any SQL database. I wonder if auto-close is off on the database they are running it against?
And when they say it is disconnecting, it is the one SSMS query window that is disconnecting in the middle of query execution, not an RDP session disconnecting or something like that? I know end users can sometimes use incorrect terms when they are trying to get support.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
September 5, 2017 at 3:32 pm
bmg002 - Tuesday, September 5, 2017 3:30 PMThe database they were connected to had auto-close set to false, correct? I believe that is a database level setting... I am just thinking if they are running a query that uses the 3 part naming (<database>.<schema>.<object>), they could be running the query from any SQL database. I wonder if auto-close is off on the database they are running it against?And when they say it is disconnecting, it is the one SSMS query window that is disconnecting in the middle of query execution, not an RDP session disconnecting or something like that? I know end users can sometimes use incorrect terms when they are trying to get support.
it is just that query window the one that shows a message that the query was forcibly close,
September 5, 2017 at 3:39 pm
A quick google of that error brought me to this:
https://technet.microsoft.com/en-us/library/ms187005(v=sql.105).aspx
basically - update SSMS, possible network issues, server setting may need changing (depending on the windows server version).
Failing that, anything interesting in the SQL (or windows) logs at the time of disconnect (on the SQL Server instance server, not the end users computer)?
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
Viewing 15 posts - 1 through 15 (of 27 total)
You must be logged in to reply to this topic. Login to reply