Solved

Timeout expired errors after upgrading from SQL Server 2005 to 2008 R2

Posted on 2012-03-18
13
734 Views
Last Modified: 2012-08-13
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.
0
Comment
Question by:mtanquary
  • 7
  • 3
  • 3
13 Comments
 
LVL 25

Assisted Solution

by:jogos
jogos earned 375 total points
Comment Utility
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.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
Comment Utility
>>The upgrade consisted of detaching the DB files from 2005 and attaching them to 2008. That's it.<<
No reindex?  Not even stats update?
0
 

Author Comment

by:mtanquary
Comment Utility
That's right. No reindex or stats update. The DBs are also still in 2005 compatibility mode.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
Comment Utility
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?
0
 

Author Comment

by:mtanquary
Comment Utility
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)?
0
 

Author Comment

by:mtanquary
Comment Utility
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
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 75

Assisted Solution

by:Anthony Perkins
Anthony Perkins earned 125 total points
Comment Utility
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
http://ola.hallengren.com/
0
 

Author Comment

by:mtanquary
Comment Utility
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.
0
 
LVL 25

Assisted Solution

by:jogos
jogos earned 375 total points
Comment Utility
<<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.
0
 

Author Comment

by:mtanquary
Comment Utility
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.
0
 
LVL 25

Accepted Solution

by:
jogos earned 375 total points
Comment Utility
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.
0
 

Author Comment

by:mtanquary
Comment Utility
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.
0
 

Author Comment

by:mtanquary
Comment Utility
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!
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
monitor queries that use too much tempdb log 20 35
SQL 2012 Syntax Error 5 23
Convert column to int 13 27
Help with SQL Query 23 39
Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now