Timeout expired errors after upgrading from SQL Server 2005 to 2008 R2
We recently upgraded from SQL Server 2005 to 2008 R2 and suddenly are getting intermittent 'Timeout expired' messages.
We have a handful of windows services that we run which periodically connect to the DB and flip a bit on or off. The DB operation is as trivial as it gets. The services do this on a periodic basis, such as every 15 minutes throughout the day.
After the switch to 2008R2, each of the services will have 1 or 2 of these 'Timeout expired' messages within a 24-hour period, and each of them at different times throughout the day.
Just for grins, I created a test program that executes the above operation on the db twice per second, and let it run for 4 hours. In one four hour stretch I did not see any timeouts. In another, I saw one. My only conclusion at this point is that the error has nothing to do with the number of calls, and I was unable to reliably force a timeout.
I would like to see if anyone can provide me some ideas on how to determine whether this is really a symptom of some other issue, such as network, etc.
We have 4 DBs on one virtualized server in a mirrored configuration. Nothing special, just a basic configuration. The DB servers are all on one physical server and the windows services are on a separate physical server within a virtualized environment. The main change was the upgrade to 2008R2. The upgrade consisted of detaching the DB files from 2005 and attaching them to 2008. That's it.
Thank you for any assistance you can provide.
We have a handful of windows services that we run which periodically connect to the DB and flip a bit on or off. The DB operation is as trivial as it gets. The services do this on a periodic basis, such as every 15 minutes throughout the day.
After the switch to 2008R2, each of the services will have 1 or 2 of these 'Timeout expired' messages within a 24-hour period, and each of them at different times throughout the day.
Just for grins, I created a test program that executes the above operation on the db twice per second, and let it run for 4 hours. In one four hour stretch I did not see any timeouts. In another, I saw one. My only conclusion at this point is that the error has nothing to do with the number of calls, and I was unable to reliably force a timeout.
I would like to see if anyone can provide me some ideas on how to determine whether this is really a symptom of some other issue, such as network, etc.
We have 4 DBs on one virtualized server in a mirrored configuration. Nothing special, just a basic configuration. The DB servers are all on one physical server and the windows services are on a separate physical server within a virtualized environment. The main change was the upgrade to 2008R2. The upgrade consisted of detaching the DB files from 2005 and attaching them to 2008. That's it.
Thank you for any assistance you can provide.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
That's right. No reindex or stats update. The DBs are also still in 2005 compatibility mode.
That's right. No reindex or stats update
So did you know that this is part a general maintenance of a database, especially when migrating from another version? Or have you considered re-indexing the database? Do you need help with that?
So did you know that this is part a general maintenance of a database, especially when migrating from another version? Or have you considered re-indexing the database? Do you need help with that?
ASKER
We did not have these tasks on our radar. I am now doing a stats update on the DBs and updating the compatibility mode. I will plan to re-index as well. What is the procedure for that (or link to trusted reference)?
ASKER
I found this script to run on each DB. Once we have this done, we'll monitor and I'll update if we see any changes. Thank you for your help thus far!
USE MyDatabase
GO
EXEC sp_MSforeachtable @command1="print '?' DBCC DBREINDEX ('?', ' ', 80)"
GO
EXEC sp_updatestats
GO
USE MyDatabase
GO
EXEC sp_MSforeachtable @command1="print '?' DBCC DBREINDEX ('?', ' ', 80)"
GO
EXEC sp_updatestats
GO
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Just checking back in. We did update stats and rebuild the indexes, but still having the issue. We'll be continuing to check things, if anyone has any more specific things we could try I would be happy to do it. Thanks.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
We have discovered that the issue has nothing to do with the 2008 upgrade we did. We just recently did some work on our production environment, which included adding mirroring and moving some of the databases to a different server. We now have the same issue going on in production which is still running 2005.
I noticed this morning one new clue. We have 7 windows services that all perform this lock/unlock on a periodic basis (as described previously), and I was thinking they all were having errors. In fact, all but one has the timeout errors occurring regularly, even though that one performs the very same SQL activity as the others.
The difference is that the one service connects to the server under a separate login as all the other services. So, now it seems that there is some kind of login related problem here.
This lead me to think that the one thing in common with the DBs that we are experiencing this issue with be it 2005 or 2008 is that we began to experience the issue after some kind of work that involved recreating the logins. I have tried combing through the login configuration and comparing on servers with and without the issue but cannot see anything different.
I'm wondering if this might spark an idea with any of you as to what I might check out.
I noticed this morning one new clue. We have 7 windows services that all perform this lock/unlock on a periodic basis (as described previously), and I was thinking they all were having errors. In fact, all but one has the timeout errors occurring regularly, even though that one performs the very same SQL activity as the others.
The difference is that the one service connects to the server under a separate login as all the other services. So, now it seems that there is some kind of login related problem here.
This lead me to think that the one thing in common with the DBs that we are experiencing this issue with be it 2005 or 2008 is that we began to experience the issue after some kind of work that involved recreating the logins. I have tried combing through the login configuration and comparing on servers with and without the issue but cannot see anything different.
I'm wondering if this might spark an idea with any of you as to what I might check out.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
That sounds like this case. You bring up a good point. I did not mention that the one service user is utilizing a SQL login while the other services are utilizing windows logins.
Sounds like we need to look into the issue being in the domain controller? I'm not even sure how to diagnose that. I'll do some research, but any ideas are welcome.
I think what I need to do is have some kind of long running monitor to watch the domain controller over a period of time and see if spikes in latency on the domain match up with the time-out time slices I'm seeing.
Sounds like we need to look into the issue being in the domain controller? I'm not even sure how to diagnose that. I'll do some research, but any ideas are welcome.
I think what I need to do is have some kind of long running monitor to watch the domain controller over a period of time and see if spikes in latency on the domain match up with the time-out time slices I'm seeing.
ASKER
Checking back in. After the last comment, we had changed the connection strings to utilize a DB login rather than a Windows login which actually did clear up our timeouts. That's the good news. The bad news is our production DBs are now doing the same thing, however it doesn't matter whether we use DB logins or Winodws Authentication, we get the same results. It really appears to be some kind of a network hiccup in the connections that is causing the problem. Oh, and the DB is still 2005 in production. We never upgraded them since we had this issue with 2008SE.
This thread began as a 2008SE upgrade issue, and the issue that we had has been identified, I'll close this thread based on that. Thanks to everyone who helped!
This thread began as a 2008SE upgrade issue, and the issue that we had has been identified, I'll close this thread based on that. Thanks to everyone who helped!
No reindex? Not even stats update?