4

I'm running SQL Server 2008 R2 on Windows server 2008 R2 and up until recently we were having no problems. After windows updates recently (no sql server updates) we cannot run step 1 of our maintenance plan DBCC CHECKDB It does not error out, it just runs indefinitely, longest time so far 36 hours. The DB is only about 500MB so I dont think size is an issue.

I was just wondering if anyone has seen this sort of issue in the past.

Edit: For what it is worth I also noticed rebuilding index and updating statistics is having the same issue. I have no idea if this helps.

Edit2: So if if it helps anyone after running SELECT * FROM sys.dm_exec_requests; I'm seeing the wait type CXPACKET on the following tasks: ALTER INDEX, DBCC;

Improve this question
8
  • 1
    what is the dbcc's request status, wait_time, wait_type, last_wait_type in sys.dm_exec_requests
    – Remus Rusanu
    Mar 20, 2013 at 21:30
  • Status: suspended, wait_time: 28309911, wait_type: CXPACKET, last_wait_type: CXPACKET
    – Nick
    Mar 21, 2013 at 13:17
  • This will help you sqlmag.com/blog/sql-server-questions-answered-28/… . BTW, can you backup the same database and restore it on some other test server and then run CHECKDB, to see if it times out or not ?
    – Kin Shah
    Mar 21, 2013 at 13:51
  • 1
    Also run "SELECT * from sys.dm_os_waiting_tasks where session_id=DBCC_ID". Where DBCC_ID is the numeric session ID for the DBCC task. If CXPACKET is the issue try this article: blog.sqlauthority.com/2010/11/13/…
    – StanleyJohns
    Mar 21, 2013 at 17:31
  • 1
    Paul Randal just blogged about CHECKDB running very slowly (by design) against databases that contain indexes on computed columns. Perhaps there was an application update? Try running the diagnostic query from the blog post. (If this is the problem, unfortunately there is no resolution, but at least it's an answer to the question.)
    – Jon Seigel
    Mar 26, 2013 at 3:19

1 Answer 1

Reset to default
1

There is a very interesting post on DBCC CHECKDB on SqlPerformance, Minimize impact of checkdb. This will give you several ideas, like testing your backup on another server (therefore offloading the charge), using several options (you can experiment with PHYSICAL_ONLY for example).

As CHECKDB is making a big use of tempdb, this you check how things are going on for this database? Is it on separate disk?

Improve this answer
3
  • Yes, it is on a NAS connected over fast ethernet so we are never expecting it to run at blinding speed but 48 hours plus on 500MB database seems like something is definitly wrong. using Physical_Only and the problem remains the same
    – Nick
    Mar 21, 2013 at 13:08
  • @Nick, do you have some funny DB parameters like autoshrink, or something else? Did you try to restore and test on another server? Could you also check this DB VLFs ?
    – KookieMonster
    Mar 21, 2013 at 13:23
  • Nothing out of the ordinary, DBCC is being called from maintenance plan but running it manually also times out.
    – Nick
    Mar 21, 2013 at 13:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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