Jaimesastre
asked on
Timeout in SQLServer 2008
Hello,
We didn't know that we have any kind of problem in our system related to the database.
But we have been analysing the timeout errors registered from the .NET aplication, we can see that sometimes the error "Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding" happens in query's with an average response time of less than 100 miliseconds.
The timeout of the .NET connection is 60s.
So seems that we have a process that cause to colapse the system.
In order to get the guilty, we want to establish a timeout for the worker processes in SQL Server, in order that anyone that use more than 50 seconds, has to be killed.
Does somebody know how to fix in SQLServer a limit/timeout for querys/processes/jobs?
Thanks in advance,
We didn't know that we have any kind of problem in our system related to the database.
But we have been analysing the timeout errors registered from the .NET aplication, we can see that sometimes the error "Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding" happens in query's with an average response time of less than 100 miliseconds.
The timeout of the .NET connection is 60s.
So seems that we have a process that cause to colapse the system.
In order to get the guilty, we want to establish a timeout for the worker processes in SQL Server, in order that anyone that use more than 50 seconds, has to be killed.
Does somebody know how to fix in SQLServer a limit/timeout for querys/processes/jobs?
Thanks in advance,
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
From SQL Server "monitoring" point of view I warmly recommend to use SQL Own Performance Dashboard Reports that can save lots of headaches - see below:
http://blogs.technet.com/b/rob/archive/2009/02/18/performance-dashboard-reports-for-sql-server-2008.aspx
http://blogs.msdn.com/b/sqlserverfaq/archive/2010/05/27/sql-server-performance-dashboard-reports-in-ssms-introduction-install-datediff-error-amp-modified-for-sql-2008.aspx
http://blogs.msdn.com/b/arvindsh/archive/2010/06/25/performance-dashboard-reports-in-sql-server-2008.aspx
http://blogs.technet.com/b/rob/archive/2009/02/18/performance-dashboard-reports-for-sql-server-2008.aspx
http://blogs.msdn.com/b/sqlserverfaq/archive/2010/05/27/sql-server-performance-dashboard-reports-in-ssms-introduction-install-datediff-error-amp-modified-for-sql-2008.aspx
http://blogs.msdn.com/b/arvindsh/archive/2010/06/25/performance-dashboard-reports-in-sql-server-2008.aspx
In order to get the guilty, we want to establish a timeout for the worker processes in SQL Server, in order that anyone that use more than 50 seconds, has to be killed.
Does somebody know how to fix in SQLServer a limit/timeout for querys/processes/jobs?
>>
You can set the Query timeout in SQL SSMS Properties on the "Connections" option like in attached picture however...I would strongly advise to NOT do that on production servers as this can cause lots of data issues - as you can see the SQL default is 600 seconds or ten minutes.
SQLProperties.png