locked
Columnstore Index in read_only filegroup prevents CheckDB RRS feed

  • 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 checkfileroup, 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?

    This behavior is reproducible with the below pseudo code.

    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



    Thursday, February 22, 2018 9:42 PM

All replies

  • We'll take a look at this, and I'll update the thread accordingly.

    -Sean


    The views, opinions, and posts do not reflect those of my company and are solely my own. No warranty, service, or results are expressed or implied.

    Friday, February 23, 2018 1:56 AM
  • Is there an update on this post?
    Thursday, January 10, 2019 7:34 PM
  • There is currently no update other than to work around the issue keep the filegroup and READ_WRITE.

    The views, opinions, and posts do not reflect those of my company and are solely my own. No warranty, service, or results are expressed or implied.

    Friday, January 11, 2019 2:34 AM
  • How do I go about raising this as bug as the statements in the columnstore-indexes design guidelines seems to suggest this pattern should be supported.

    https://docs.microsoft.com/en-us/sql/relational-databases/indexes/columnstore-indexes-design-guidance?view=sql-server-2017#use-table-partitions-for-data-management-and-query-performance

    Wednesday, January 16, 2019 1:56 AM
  • Wednesday, January 16, 2019 9:18 AM