Random Timeout around same time sql 2008

Question

Random Timeout around same time sql 2008

derek clinton

SSC Rookie

Points: 44
More actions
December 30, 2010 at 10:19 am

#229115

We migrated from sql server 2000 to sql server 2008. We get random Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding. errors every half an hour. We have turned of sql agents running and still can't figure it out. Must be a setting or something. Any ideas? Could something be running every half hour that we are not aware of new to SQL 2008?
Thanks,

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

WayneS SSC Guru Points: 95460 More actions · Answer 1

We had something similar, but it was a network issue. If I remember correctly, it was dueling domain controllers(?) generating different information. It was also only happening every 4 hours, not every 1/2. But still, something to check.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

derek clinton SSC Rookie Points: 44 More actions · Answer 2

Thanks for the reply Wayne. We only have one domain controller. Still looking into this. Can't find anything that is causing the system to timeout every 30 minutes. grr 🙂

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

Run a Profiler trace? If something is running against the server that can catch it. If not, then you've eliminated one possibility.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Jay Milo SSC Enthusiast Points: 158 More actions · Answer 4

I'm a co-worker of the guy who opened this post.

We found what was causing our issue; a query coming from an outside source (Moodle). The query is apparently "standard" and runs from a cron job on their side. It had 20,000 varchar values in a WHERE usrname IN () clause. Still not clear what resources it was using up, but when it was running other queries running from our webserver against a different database but same server/instance simply would not complete without a 60 second timeout window.

I temporarily "fixed" this by removing and recreating the Moodle-specific user every 30 minutes so the login would fail. After a day of doing this, I wanted to take another look and just like magic, the offending query disappeared. We opened a helpdesk ticket with Moodlerooms, but have not seen the bad query in 3 days now.

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 5

run sp_whoisactive (find it online) while the bad query is running to see what it is waiting on or blocking.

also look at the query plan it creates - you might be able to do something about that as well.

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service