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.
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Your timeouts are not mappable on moments of backups, .....

You recently upgraded to 2008R2
- are your databases also changed to the compatibility level of 2008R2 or are they still 2005?
- new instance so : memory settings , maintenance , tempdb  .... possibly are not set to the  performance-level of your 2005 instance

And as for any timeout (performance-issue) it's only measuring and monitoring all events (error log, event viewer) that can guide you to the real bottleneck memory, disk IO, network, cpu , locks .....  and if for example a new/changed index can be the miracle-sollution.
Anthony PerkinsCommented:
>>The upgrade consisted of detaching the DB files from 2005 and attaching them to 2008. That's it.<<
No reindex?  Not even stats update?
mtanquaryAuthor Commented:
That's right. No reindex or stats update. The DBs are also still in 2005 compatibility mode.
IT Pros Agree: AI and Machine Learning Key

We’d all like to think our company’s data is well protected, but when you ask IT professionals they admit the data probably is not as safe as it could be.

Anthony PerkinsCommented:
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?
mtanquaryAuthor Commented:
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)?
mtanquaryAuthor Commented:
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
EXEC sp_MSforeachtable @command1="print '?' DBCC DBREINDEX ('?', ' ', 80)"
EXEC sp_updatestats
Anthony PerkinsCommented:
That is the "brute" force method for re-indexing and is also deprecated.  For a better approach check out this link:
SQL Server Backup, Integrity Check, and Index and Statistics Maintenance
mtanquaryAuthor Commented:
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.
<<The DBs are also still in 2005 compatibility mode.>>
Why? Having it in the level of your db-engine is taking advantage of also the enhancements in that engine.

And for other ideas .. first comment all over again.  Measure the overall performance of your new server and see where it's not yet up to speed.  If for example your memory is not yet configured you generate to much IO which is slower ... keeping locks longer... combine that with allocating space (db or log growing ...).

Starting to monitor your new 2008 environment it's maybe good to take a look at data collector http://www.databasejournal.com/features/mssql/article.php/3779846/Reports-for-SQL-Server-2008-System-Data-Collections.htm

But don't forget there could also be a 'virtualized' side of the problem.
mtanquaryAuthor Commented:
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.
Don't see an issue if it's a SQL login. But when its a windows-login then you always can have a network-delay when going to AD.  I've seen simmilar timeouts  caused by an inpropper process that went balistic on a domain controler, but because it was realy for some minutes it was 'easy' to track.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
mtanquaryAuthor Commented:
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.
mtanquaryAuthor Commented:
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!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.