Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

Our database server has a maintenance plan which runs each night:

  1. Back up databases & logs (10-20GB max)
  2. Check Database Integrity
  3. Rebuild Indexes
  4. Update Statistics

Normally this all executes in under a minute per database. However, in the last month there have been two instances where it just froze the database for 3 hours. The DBCC statement took 2 hours to complete, but completed with no errors. This whole time our database was rejecting connections.

Aside from these two isolated incidents, this database has never given a single problem.

Could anyone suggest what may cause such a problem?

share|improve this question
1  
Which version? Rebuild Indexes (normally really not needed every night) is a blcoking operation on non-enterprise (and enterprise if you formualte the command wrong). This can lead to long blocking. –  TomTom Oct 13 '10 at 15:37
    
2005 Standard. Interesting about the Rebuild Indexes. How often would you recommend running that? –  realworldcoder Oct 13 '10 at 15:42
    
Update: Discovered that doing a Disk Check on C: is failing. Nothing in the error logs about HDD problems, but have scheduled an offline chkdsk as the next step. –  realworldcoder Oct 14 '10 at 17:05
add comment

3 Answers 3

Nicholas is right in that you usually (there are always exceptions) don't need to do index rebuilds every night (especially if you have Standard edition, which won't allow you to do online rebuilds).

You might want to ditch the maintenance plans, especially in regards to index defrag and statistics rebuilds. I would go with a script that looks at the fragmentation level of your indexes and decides whether it should be rebuilt or not. Michelle Ufford (@SQLFool) has an excellent script that allows you to have nightly jobs that check the fragmentation levels and only rebuilds index that meet a certain level of fragmentation (set by you). You can find the script here: http://sqlfool.com/2010/04/index-defrag-script-v4-0/. This script will also update statistics if needed. There are many other scripts like this out there.

HTH, Dan

share|improve this answer
add comment

It sounds like it could be a sudden increase in the growth of database files? Have you checked the SQL Server Error_Log and just wondering can you shrink the logs prior to the maintenance window each night?

share|improve this answer
    
The database file is 90% free space and the log file is shrunk each time it is backed up (which is hourly.) +1 because an auto-grow did cause this problem once before. –  realworldcoder Oct 13 '10 at 15:41
    
Presumably you now pre-allocate your space on not rely upon auto-grow. –  jl. Oct 13 '10 at 17:50
add comment
up vote 1 down vote accepted

Answering my own question:

After running an offline chkdsk, only to have it recur again a week later, we diagnosed this as a hardware failure or low level OS problem, and requested a new server from our ISP.

After we restored the exact same database to our new server, it has now been running flawlessly for 4 months with the only difference being the hardware & OS installation. Case closed.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.