dbcc checkdb in suspended status

In a SQL Server 2008 instanece, I'm running a maintenance plan that does a DBCC CHECKDB (<myDB>) WITH NO_INFOMSGS. It ran successfully on all databases but the last one. It is now suspended for hours on the DBCC CHECKDB command (PAGEIOLATCH_EX wait) on the last database in the plan.

WHy is the DBCC CHECKDB suspended? HOw can I stop the check?  Will that "break" anything? What does this mean when dbcc is in a SUSPENDED status?    How can I can I keep this from happening again?

I do have a full backup of the database. But I hope I don't have to use.

I had done a full backup of the databse and its transaction log, then kicked of the checkdb.

After the t-log backup completed, I shrunk the log file with this command:
USE [MiniServiceBroker]
GO
DBCC SHRINKFILE (N'MniServiceBroker_LOG' , 0, TRUNCATEONLY)
GO
data_bitsdbaAsked:
Who is Participating?
 
jmcvinneyCommented:
You're stuck waiting for an exclusive lock on the disk system at a certain data page.  You can kill the dbcc if you want without any issues.  The particular lock is caused by the disk either being too busy or that page being in constant use.  To find the process that is causing the halt, you can run the standard blocking processes report (right click the DB->reports->standard report->ALL Blocking Transactions)
0
 
Anthony PerkinsCommented:
>>I shrunk the log file with this command:<<
Unrelated, but this is a very bad idea.  All you are doing is fragmenting your Transaction log and rendering the Transaction Log backup useless.  If you cannot maintain a database with Full Recovery Model correctly, then seriously consider changing it to Simple Recovery Model.
0
 
Anthony PerkinsCommented:
Incidentally according to SQL Server's BOL TRUNCATEONLY has no effect on the Transaction Log file, it is only intended for data files.  Although I note that Kimberly Tripp, Tom LaRock, Glenn Berry, do not appear to consider that relevant.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
data_bitsdbaAuthor Commented:
jmcvinney,

Thank you for your response. I'm trying to do as you directed but I'm getting an error when I try to run the report: "The file 'Microsoft.ReportViewer.WInForms, Version=9.0.0.0,Culture=neutral,PublicKeyToken=b0lkkj888888' cannot be opened. Do you want to remove the reference to it from the Recent list?"

I didn't install this environment so I'm not sure if everything needed got installed. I'm  a newbie SQL Server person, if you haven't notice. :-)

I clicked on 'Yes' and tried again but got the same error. What do you recommend?

Bits
blocking-error-1.bmp
0
 
data_bitsdbaAuthor Commented:
Looks like there is a work-aorund by downloading and installing the Reporting Services Report Viewer 9.0, at the link: http://www.microsoft.com/downloads/details.aspx?displaylang=en&FamilyID=6ae0aa19-3e6c-474c-9d57-05b2347456b1

Then I'll be able to view the all blocking transactions report.

Thanks!
0
 
jmcvinneyCommented:
ok, just do

select * from master..sysprocesses

The number in the blocked column is the blocking process.  Trace it back until you find the one that has a 0 in the blocked column.

So for example:
SPID      KPID        Blocked
69      0      33      0x0000      0      PAGEIOLATCH_EX

would tell me to look at SPID 33 so I scroll up and see
SPID      KPID        Blocked
33      0      72      0x0000      0      PAGEIOLATCH_EX

Which tells me to look at SPID 72
SPID      KPID        Blocked
72      0      0      0x0000      0      EXECUTING

72 is the culprit, so I look further to the right and it will tell me what it is doing, in this case, the cmd column says "SELECT" so I have a big query running and I just kill it off, which allows 33 to execute, followed by 69 (or both if 33 is not blocking)
                                                                                              
0
 
data_bitsdbaAuthor Commented:
I did get Reports viewer installed and it said there were no blocking transactions for the database. I ran the query that you provided and here are the results. I didn't see a spid of 2160, though.

61      2160      0      0x0044      0      PAGEIOLATCH_EX                        2:1:714346                                                                                                                                                                                                                                                            12      1      2116688      29276193      2      2011-12-05 13:40:42.703      2011-12-05 13:40:42.703      0      1      suspended                           0x0101000000000005140000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000      FARSQL                                                                                                                           Microsoft SQL Server Management Studio                                                                                                2776            DBCC SSB CHECK        NT AUTHORITY                                                                                                                          NETWORK SERVICE                                                                                                                       01F9D0B2D4A4      LPC               NT AUTHORITY\NETWORK SERVICE                                                                                                          0x0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000      0x01000B0051047E30F02F6D490100000000000000      0      -1      0
0
 
jmcvinneyCommented:
OK, so you have physical IO problems.  Is your disk spinning continually?  Is this a virtual or physical server?  There's a process outside of SQL that is blocking SQL.

2160 is the worker thread (KPID), the next 0 is the blocked column
0
 
data_bitsdbaAuthor Commented:
It is a virtual server. I can ask the Windows admin to look at it.
0
 
jmcvinneyCommented:
You may be being blocked by a process from another virtual machine that is accessing the same disk.  That's one of the big drawbacks of virtualizing SQL
0
 
data_bitsdbaAuthor Commented:
Can I safely issue the "kill 61" on the dbcc checkdb command? It doesn't look like it will complete anytime soon. If the Windows admin need to see the problem in action, I can issue the command on this database again.
0
 
jmcvinneyCommented:
yes, checkdb runs as a transaction so it will kill safely
0
 
data_bitsdbaAuthor Commented:
Our Server admin says he doesn't see anything running on the server with 2160 as the worker thread.
0
 
data_bitsdbaAuthor Commented:
I killed the process, strangely it shows that it completed in the SQL Server Log.

I would have expected an error or something:
Date            12/6/2011 3:07:05 PM
Log            SQL Server (Current - 12/6/2011 3:03:00 PM)

Source            spid61

Message
DBCC CHECKDB (MniServiceBroker) WITH no_infomsgs executed by NT AUTHORITY\NETWORK SERVICE found 0 errors and repaired 0 errors. Elapsed time: 25 hours 26 minutes 19 seconds.  Internal database snapshot has split point LSN = 00002746:00000051:0001 and first LSN = 00002746:00000050:0001.

The Activity Monitor was showing lots of waits in the BufferIO wait category, and the admin did say the memory was pegged for the server. Do you think this is all due to a memory constraint instead of a disk subsystem issue?
0
 
jmcvinneyCommented:
I'd still believe it was a disk subsystem issue.  As things are waiting to be flushed to disk, they reside in memory on the server.

You don't get an error unless you did DBCC checkdb () with no_infomsgs, all_errors
0
 
jmcvinneyCommented:
2160 was the threadid, not the processID.  It will be a thread within the SQL Server process.  You'd see it using the utilities from sysinternals (http://www.sysinternals.com)
0
 
data_bitsdbaAuthor Commented:
Working with a different WIndows Server Admin, I found that the underlying disks that the database and transaction logs reside on had been formatted with 4K allocation units. I've read that Microsoft recommends 64K allocation units. Would having to go read more smaller blocks of data cause the issues that I've been having?
0
 
jmcvinneyCommented:
Not necessarily.  The reason that microsoft recommends 64K allocation units is because a data page is 8K and 8 pages is an extent, for a total of 64K, allowing reads to be located in the same area on disk.  It's certainly a best practice to format 64K allocation units, but it won't make or break you in most cases.  It certainly wouldn't cause DBCC to just hang.  Have you tried running DBCC again to see if it hangs again?  
0
 
data_bitsdbaAuthor Commented:
There is now more information to go on to find the root cause.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.