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.
Best Answer
Update:
The fix has been released in Sql Server 2008 r2 sp1 cu4.
Cumulative update package 4 for SQL Server 2008 R2 Service Pack 1
More details about the issue are available here:
FIX: Access violation when you run a DBCC CHECKDB command against a database that contains a table that has a spatial index in SQL Server 2008 or in SQL Server 2008 R2
History:
We filed a support case with Microsoft about the issue. Apparently it is a known issue that has been fixed in other versions of Sql Server (2008 SP2 CU7, 2008 SP3 CU3, 2008R2 RTM CU11, 2008R2 SP1 CU4) and will be fixed in Sql Server 2008R2 SP1 CU4 (sp1 Cumulative Update 4).
So the solution for now is to not run "WITH EXTENDED_LOGICAL_CHECKS" or skip "DBCC CHECKDB" altogether until the cumulative update is out in mid December 2011.