Solved

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

Posted on 2012-03-18
13
745 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 3
  • 3
13 Comments
 
LVL 25

Assisted Solution

by:jogos
jogos earned 375 total points
ID: 37737084
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
ID: 37738818
>>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
ID: 37738902
That's right. No reindex or stats update. The DBs are also still in 2005 compatibility mode.
0
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 37738960
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
ID: 37739038
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
ID: 37739333
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
 
LVL 75

Assisted Solution

by:Anthony Perkins
Anthony Perkins earned 125 total points
ID: 37740276
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
ID: 37761488
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
ID: 37767124
<<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
ID: 37788655
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
ID: 37789085
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
ID: 37789192
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
ID: 37833206
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

SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Add a step to a system backup job 6 55
SQL Server 2005 database messed up. Can it be fixed? 4 39
How can I use this function? 3 35
How do I partition this table on date? 5 55
Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…
Finding and deleting duplicate (picture) files can be a time consuming task. My wife and I, our three kids and their families all share one dilemma: Managing our pictures. Between desktops, laptops, phones, tablets, and cameras; over the last decade…

710 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