Questions[checkdb](server)

This one's for you, Paul! :-)

I have a customer which has two databases for two of his office locations. Both are roughly the same size (around 10 GB), both are hosted at the same professional hoster's site - so give or take, they're the same.

Yet, database A completes its Check database integrity step in the maintenance plan in less than 30 minutes, while database B never even completes - the DBA killed the process after more than 3 hours of running.

These steps are being run "after hours" - so there shouldn't be any significant activity on the servers.

Any explanation? Any idea what to check for or where to look for a reason for this (and a way to solve it)? There are no error reported or anything.....

We've been having an issue with DBCC CHECKDB crashing with an access violation (null pointer deference) on Sql Server 2008R2 when spatial indexes are present. It is repeatable with DBCC CHECKDB WITH EXTENDED_LOGICAL_CHECKS but also happens in some cases with just DBCC CHECKDB.

This is occurring for us on Sql Server 2008 R2 sp1 standard (and developer edition) under Windows 2008 R2 and under Windows 7 (all 64 bit).

Here's a simple T-SQL script that demonstrates the issue. If you run it in the SSMS you'll see the output and the sql connection getting terminated.

use master
go
if exists(select * from sys.databases where name = 'DbccCrashExample') drop database DbccCrashExample
go
create database DbccCrashExample
GO
use DbccCrashExample
go
CREATE TABLE dbo.GeometryTable
(
    GeometryTableID int NOT NULL,
    Feature geometry NOT NULL,
    CONSTRAINT PK_GeometryTable PRIMARY KEY CLUSTERED (GeometryTableID)
)
GO
insert into dbo.GeometryTable(GeometryTableID, Feature)
select 1, geometry::STGeomFromText('POINT(0 0)', 4326)
go
create spatial index SPATIAL_GeometryTable_Feature on dbo.GeometryTable(Feature) with (BOUNDING_BOX=(0, -2, 1, 2))

-- works
--DBCC CHECKDB

-- fails
DBCC CHECKDB WITH EXTENDED_LOGICAL_CHECKS

One strange thing is that it appears that DBCC has completed without errors, but then a severe error occurs:

CHECKDB found 0 allocation errors and 0 consistency errors in database 'DbccCrashExample'.
Msg 0, Level 11, State 0, Line 0
A severe error occurred on the current command.  The results, if any, should be discarded.
Msg 0, Level 20, State 0, Line 0
A severe error occurred on the current command.  The results, if any, should be discarded.

In the Sql Server error log we get a stack dump like this:

SqlDumpExceptionHandler: Process 59 generated fatal exception c0000005 EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process.                                                                                        
* *******************************************************************************                                
*                                                                                                                
* BEGIN STACK DUMP:                                                                                              
*   11/20/11 13:23:34 spid 59                                                                                    
*                                                                                                                
*                                                                                                                
*   Exception Address = 0000000000E84A8D Module(sqlservr+0000000000274A8D)                                       
*   Exception Code    = c0000005 EXCEPTION_ACCESS_VIOLATION                                                      
*   Access Violation occurred reading address 0000000000000000                                                   
* Input Buffer 408 bytes -                                                                                       
*             create spatial index SPATIAL_GeometryTable_Feature on dbo.Geo                                      
*  metryTable(Feature) with (BOUNDING_BOX=(0, -2, 1, 2))   -- works --DBCC                                       
*  CHECKDB  -- fails DBCC CHECKDB WITH EXTENDED_LOGICAL_CHECKS                                                   
*                                                                                          

We have opened a paid support case with Microsoft about the issue. I am posting this problem here to share the issue with others. I will post what we hear back from Microsoft on the issue.

We have a mix of SQL 2000, 2005, and 2008 servers, and we've always run a DBCC CHECKDB nightly just before the full backups, under the theory that you want to make sure the DB is in good shape before you back it up. (Obviously, full verification of backups can only be done via test restores, but thats a slightly different topic.)

Assuming that I can't offload the DBCCs to a backup server or something (which would be ideal), is DBCC CHECKDB followed by FULL BACKUP the best sequence?

The only "best practices" document I found that discuss this was a Best Practices for SQL Server Maintenance for SAP from 2006 I found on TechNet:

Ideally, a consistency check using DBCC CHECKDB sould be run before performing an online database backup.

Is this advice correct? Is it correct for all versions of SQL?

(In case this helps, part of the motivation for asking this is that the DBCC runtime seems to vary a fair amount from night to night, so we can't rely on exactly when the backups will be complete, which makes scheduling our tape archive jobs difficult. Also, if maintenance runs long and has to be cancelled for any reason, I'd much rather that the backup be reliably complete than the DBCC.)

I have a 2Tb+ SAP database with 8 datafiles, the first files have grown to fill the available disk space on our HP EVA5000, and the growth is now on the new files. We upgraded SQL from SQL server 2000 to 2005 and Checkdb works differently, and now fails with no space to create snapshot. We have added 50Gb to each file as a temporrary measure while we precure a new I/O subsystem. The Question is: How can we monitor snapshot usage? What will happen if Checkdb starts now it has more space, then fills up? and can you cancel CheckDb if you see that its going to run out of space?

I am running CheckDB with REPAIR_ALLOW_DATA_LOSS on a Shairpoint search DB that is 47mb

It has been running for > 30min. Is that normal?

How long should it take?