Solved

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

Posted on 2012-03-18
13
735 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
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
 
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
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
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 …
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…
Delivering innovative fully-managed cloud services for mission-critical applications requires expertise in multiple areas plus vision and commitment. Meet a few of the people behind the quality services of Concerto.

919 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

11 Experts available now in Live!

Get 1:1 Help Now