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




Array

No comments yet... Be the first to leave a reply!

Software Reviews | Book Reviews | FAQs | Tips | Articles | Performance Tuning | Audit | BI | Clustering | Developer | Reporting | DBA | ASP.NET Ado | Views tips | | Developer FAQs | Replication Tips | OS Tips | Misc Tips | Index Tuning Tips | Hints Tips | High Availability Tips | Hardware Tips | ETL Tips | Components Tips | Configuration Tips | App Dev Tips | OLAP Tips | Admin Tips | Software Reviews | Error | Clustering FAQs | Performance Tuning FAQs | DBA FAQs |