How to Shrink a Database with Column Store Index

DBCC SHRINKDATABASE will fail when it encounters a columnstore index. To complete DBCC SHRINKDATABASE, disable all columnstore indexes before executing DBCC SHRINKDATABASE, and then rebuild the columnstore indexes.

1. Disable columnstore indexes.

The below query will list of the column store indexes for a given database

SELECT *
FROM sys.indexes WHERE TYPE_DESC
LIKE '%COLUMNSTORE%'

The following is the result of the above query.

The disable script can be built using the following script.

SELECT 'ALTER INDEX [' + Name + +'] ON ' + OBJECT_NAME(object_id) + ' DISABLE'
FROM sys.indexes WHERE TYPE_DESC
LIKE '%COLUMNSTORE%'

Output of the query is shown below.

ALTER INDEX [IndFactResellerSalesXL_CCI] ON FactResellerSalesXL_CCI DISABLE
ALTER INDEX [NonClusteredColumnStoreIndex-20160622-204327] ON DimEmployee DISABLE
ALTER INDEX [NonClusteredColumnStoreIndex-20160622-210321] ON FactInternetSales DISABLE

Copy and execute this query.

2. As your column store indexes are disabled now, you can execute your shrink statement which will now succeed.

3. To enable indexes again execute the following query.

SELECT 'ALTER INDEX [' + Name + +'] ON ' + OBJECT_NAME(object_id) + ' REBUILD'
FROM sys.indexes WHERE TYPE_DESC
LIKE '%COLUMNSTORE%'
ALTER INDEX [IndFactResellerSalesXL_CCI] ON FactResellerSalesXL_CCI REBUILD
ALTER INDEX [NonClusteredColumnStoreIndex-20160622-204327] ON DimEmployee REBUILD
ALTER INDEX [NonClusteredColumnStoreIndex-20160622-210321] ON FactInternetSales REBUILD

]]>