0

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?

Improve this question
3
  • 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, 2010 at 15:37
  • 2005 Standard. Interesting about the Rebuild Indexes. How often would you recommend running that?
    – realworldcoder
    Oct 13, 2010 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, 2010 at 17:05

3 Answers 3

Reset to default
2

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

Improve this answer
1

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?

Improve this answer
2
  • 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, 2010 at 15:41
  • Presumably you now pre-allocate your space on not rely upon auto-grow.
    – jl.
    Oct 13, 2010 at 17:50
1

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.

Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .