Columnstore Index in read_only filegroup prevents CheckDB

Question

It appears setting a filegroup to read_only prevents dbcc checkdb for the entire database if the filegroup contains a columnstore index. When attempting to run checkdb or checkfilegroup (for any filegroup in the database, including read-write secondaries and [PRIMARY]), the below error is returned...

Msg 8921, Level 16, State 1, Line 24
Check terminated. A failure was detected while collecting facts. 
Possibly tempdb out of space or a system table is inconsistent. Check previous errors.

Is there a supported method for having columnstore data in a read-only filegroup? Or am I precluded from integrity checks in this scenario?

Repro

create database check_fg_ro
go
use check_fg_ro
go
exec sp_changedbowner 'sa';
go
alter database check_fg_ro add filegroup check_fg_ro_2;
alter database check_fg_ro
    add file (
         name='check_fg_ro_2'
        ,filename='C:\check_fg_ro_2.ndf'
    ) to filegroup check_fg_ro_2;
go
create table foo ( 
    i int not null primary key
) on check_fg_ro_2;
go
create columnstore index ccix_foo on foo(i);
go
use master
go
alter database check_fg_ro modify filegroup check_fg_ro_2 read_only;
go
dbcc checkdb( check_fg_ro ) with no_infomsgs, all_errormsgs, extended_logical_checks;
/*
Msg 8921, Level 16, State 1, Line 24
Check terminated. A failure was detected while collecting facts. 
Possibly tempdb out of space or a system table is inconsistent. Check previous errors.
*/
go

---

_{Disclaimer: cross-posted to technet forums}

Answer 1

The problem occurs when DBCC tries to verify a deleted bitmap for a read-only columnstore table.

Deleted bitmaps are stored on the same filegroup as the columnstore table. They track rows logically deleted from compressed row groups.

As far as I can tell, everything is organized correctly in the internal system tables (on SQL Server 2017 CU3), and most of the DBCC code correctly accounts for the hidden rowsets that hold the columnstore deleted bitmaps.

For some reason, a check for offline or read-only file groups results in an unhandled exception:

Msg 8921, Level 16, State 1, Line 69
Check terminated. A failure was detected while collecting facts.
Possibly tempdb out of space or a system table is inconsistent.
Check previous errors.

The same offline/read-only check is performed several times earlier in DBCC processing (when facts are being collected) without issue.

The problem occurs when DBCC CHECKDB or DBCC FILEGROUP is run (on any filegroup), or DBCC CHECKTABLE is asked to check a specific read-only columnstore table. None of these should produce a fatal error condition that prevents the rest of the DBCC checks running, so this must be a bug.

---

Or am I precluded from integrity checks in this scenario?

As a workaround, run DBCC CHECKFILEGROUP on the columnstore filegroup immediately before it is made read-only (or run DBCC CHECKDB at that time) then:

0. DBCC CHECKALLOC on the database
1. Run DBCC CHECKCATALOG
2. Run DBCC CHECKTABLE for each table (excluding columnstore tables on a read-only filegroup)
3. You may also want to run DBCC CHECKCONSTRAINTS.

See Consistency Checking Options for a VLDB by Paul Randal and the Q & A Dividing DBCC CHECKDB over multiple days.

---

This bug was fixed in SQL Server 2019 CU 8 (bug reference 13598925). It has been back-ported to SQL Server 2017 but there is no CU entry for it.