February 26, 2009 at 3:13 pm
We are running a dedicated SQL Server 2005 server with multiple databases for different applications running on it. One is a .net application that on logging into the application, makes a SQL connection and then 3 calls to the database to build XML files to run the menu's, etc of the application pages. The last 2 mornings around 6AM this application has started returning "Timeout expired. The timeout period elapsed prior to completion of the operation" to the user when they make the initial connection. We added another database, not the one timing out, to the server that does a decent workload last week, but it ran almost a week before we started getting this problem. Other than that, there really have not been any changes made to the server or SQL recently.
I have read on other posts here as well as on other sites, that this error occurs when the .net CommandTimeout setting is reached. I verified with our programmers, and that setting is at 30 seconds. The thing is the code they are running against the database is very simple an only against a table with about 2500 rows in it. when I run all three queries against the database from a query window on my client, they all three return in 1 second. Plus when hundreds or users are on the system later in the day, we have no problems.
Here is where the situation gets a little strange. When we are getting these timeout errors on the production database, the development code which is the same as production for this module runs flawlessly against the development database that is also on this server in the same Instance. Once it starts happening, it also appears to happen to everyone that is trying to connect through the application. I was able to connect to the production database through EM and query it, and it ran quickly, although I did not have the actual query at the time and so I did not query the same table. Plus all other databases on this server continued to function correctly and we had no complaints of anyone having performance problems or timeouts other than the one database through the one application. The first time we got the issue SQL Server seemed to be responding pretty well and I could not find a problem on my end. A manager told us to reboot the SQL Server, which I did not think would fix the problem, but it did. The rest of the day, not a single timeout, or at least no reported timeouts.
Then again this morning someone checked the app around 5:20AM and it worked fine and then around 5:50AM someone checked it and we were getting timeouts. The server was rebooted and it again fixed the problem. We have been running all day without issue. The thing is nothing really should be going on at 6AM. Even the early users should not really be in the system until 7AM and I am not aware of any large load processes that run early in the morning for any of the other databases either. We looked at the basic server performance and it was not laboring at all. There really should not be all that much activity at that time at all.
I know I have been longwinded in explaining my issue, but I really do not know what to think about the odd behaviors of this one database and why a reboot fixes the problem. Does anyone have any ideas on what may be happening. If it happens again tomorrow morning, armed with my new understanding of the overall situation, I am going to try and dig a little deeper. But this has to be up by 7AM, so if nothing by then I guess another reboot will occur. Any advice or suggestions would be appreciated. Thanks.
Here is a piece of the event log entry from the user who it failed on this morning.
-----------
Event Type:Error
Event Source:CLLNET Error
Event Category:None
Event ID:100
Date:2/26/2009
Time:5:54:25 AM
User:N/A
Computer:XXXXXXXXXXX
Description:
Timestamp: 2/26/2009 10:54:25 AM
Message: HandlingInstanceID: e7e64ccf-9e09-49cc-a976-2f7a9572aeeb
An exception of type 'System.Data.SqlClient.SqlException' occurred and was caught.
----------------------------------------------------------------------------------
02/26/2009 05:54:25
Type : System.Data.SqlClient.SqlException, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089
Message : Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.
Source : .Net SqlClient Data Provider
Help link :
Errors : System.Data.SqlClient.SqlErrorCollection
Class : 11
LineNumber : 0
Number : -2
Procedure :
Server : XXXXXXXXXXXXXXX
State : 0
ErrorCode : -2146232060
Data : System.Collections.ListDictionaryInternal
TargetSite : Void OnError(System.Data.SqlClient.SqlException, Boolean)
Stack Trace : at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
....
---------------
Thanks,
TJP8
February 26, 2009 at 6:39 pm
you should check the server for blocking issues and review SQL Server Error log for any anomally
* Noel
February 26, 2009 at 7:21 pm
There have been no error messages on the server in either the SQL Logs or the Event Logs that seem to correlate with this. I definitely will look at blocking issues tomorrow morning if the problem repeats itself. I did not look at those prior to rebooting the server on Tuesday. Thanks for the reply
TJP8
February 27, 2009 at 1:49 am
February 27, 2009 at 5:52 am
I ran into this myself last week. Practically the same symptom you have, except I didn't reboot anything. The problem cleared up on it's own. In my case it's a developer running a .Net app interactively, in debug mode, and it goes along just fine then suddenly stalls for no apparent reason. I watched the server, and even ran a Profiler trace while the problem was occurring and cannot see any problem.
When it was switched to run on Development the app ran just fine.
This app updates about 6500 records in the DB. The place where it stalls was different every time it occurred, and might differ by a thousand records.
I wish I could keep duplicating the error so I could figure out what happened. I did suspect .Net ODBC drivers though.
Chris
Learning something new on every visit to SSC. Hoping to pass it on to someone else.
February 27, 2009 at 6:38 am
It happened again this morning around 5:30. I ran the simple queries from SQL EM and they ended up being blocked by a session coming off of the server. I think I have traced this session back to an SSIS job that seemed to be hung. That SPID was had a wait type Lock_M_IS and was being blocked by SPID -2 while trying to do a select * from the same table I was attempting to query. After I killed this session the event log showed a failed SSIS package and users were able to log in again successfully.
On one post here I saw someone mention that they thought a SPID -2 was an orphaned MSDTC Transaction. Overall so far most of my searching for an answer on SPID -2 has not been conclusive. Can anyone shed a little more light on this?
Thanks
TJP8
February 27, 2009 at 5:50 pm
I hope explanation at below mentioned link answers ur question:
http://www.eraofdata.com/blog/2008/12/orphaned-msdtc-transactions-2-spids/
Manu
June 3, 2009 at 12:10 pm
I'm having a similar issue, but my hunch is that it has nothing to do with SQL Server. I'm getting SQL timeout errors in my ASP.Net logs, but always on login, and logging in never takes more than 2 seconds when it works. It seems that when somebody loses their session (InProc) due to hitting the timeout set in the web.config (8 hours), they get sent back to the login page properly, but from there the error occurs when they click the button to log in. I think it's not actually sending anything to SQL Server, it's a faux error message.
Has anybody seen this before?
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply