November 2, 2011 at 8:14 pm
Hi all,
I am seeing the below errors a lot on my db server. Can anyone throw some light on me on where to start investigating for this error. I get this error as an alert from our webserver.
System.Data.SqlClient.SqlException: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlDataReader.ConsumeMetaData()
at System.Data.SqlClient.SqlDataReader.get_MetaData()
at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)
at System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior)
at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet)
“If your actions inspire others to dream more, learn more, do more and become more, you are a leader.” -- John Quincy Adams
November 2, 2011 at 9:36 pm
Please check the SQL server Error Log. Identify what was the process running the same time you got this error.
November 2, 2011 at 9:46 pm
I dont see any logs for this in sql server logs or in the eventvwr. The problem is we never know when we will get this error so that I can have profiler running so that I can capture the trace to see what has happened then.
There are blogs that say there could be uncommitted transactions. But without knowing the cause how can i come to a conclusion?
Wondering if there are ways to identify what happened then (when I got this error).
“If your actions inspire others to dream more, learn more, do more and become more, you are a leader.” -- John Quincy Adams
November 2, 2011 at 9:56 pm
First some how you need to identify the time of this error. So atleast you can find out the long running query or proc.
Check all your table statistics are up to date.
November 2, 2011 at 10:02 pm
I know what time this error occured. But dont know what was happening on SQL Server then.
“If your actions inspire others to dream more, learn more, do more and become more, you are a leader.” -- John Quincy Adams
November 2, 2011 at 10:04 pm
I got the error as an alert. Not during office hours and I wasnt in front of a computer then. I checked the error almost after an hour and half.
“If your actions inspire others to dream more, learn more, do more and become more, you are a leader.” -- John Quincy Adams
November 2, 2011 at 10:08 pm
Try to reproduce the same error with the help of the developer. Turn on the profiler and look out for the processes that are blocking using sp_who2.
Find the query which is long running.
Also check the time out settings in the application.
Better create some custom scripts to monitor the DB process 24X7 and write down all the sp_who2 output to a text file regularly. So that at any time you can back track the process based on the time when the error poped. No need to reproduce the issue.
This will help you to trouble shoot the issues in future.
Thanks.
November 3, 2011 at 1:31 am
Thanks for the information...It's helps me lot.
November 3, 2011 at 3:13 am
we get this from time to time, its usually down to locking, this has now gone away 99% of the time due to better indexes speeding up the queries so they dont touch the objects for as long.
the developer should be able to get the t-sql or proc in the error email where it timesout
otherwise like the above poster said generate custom monitoring scripts to monitor 24/7 or download the free tirals of either Idera SQL Diagnostic Manager, Red-Gate SQL Monitor or Quests Spotlight for SQL server, they are fully functional for between 14 - 30 days depeding which you get and they will help you in the right direction as they will monitor 24/7 so you can go back in time, just make sure you set Spotlight if you use this one, to store 14 days worth of playback information if it doesnt happen that frequently so you can see if it is always the same proc/code causing the timeout
November 3, 2011 at 3:16 am
You've got poorly performing queries.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 3, 2011 at 9:37 am
Thanks so much for the info everyone...Right now I dont wanna go with the idea of running profiler all day and we are waiting on approvals for the third party monitoring tools. I just found an article on mssqltips...thought to share it so that it could be helpful for those in need...
http://www.mssqltips.com/sqlservertip/1861/sql-server-monitoring-scripts-with-the-dmvs/">
http://www.mssqltips.com/sqlservertip/1861/sql-server-monitoring-scripts-with-the-dmvs/
Thanks again
“If your actions inspire others to dream more, learn more, do more and become more, you are a leader.” -- John Quincy Adams
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply