DBCC Commands for SQL Server Analysis Service 2016

As a SQL Server DBA, we are familiar about the DBCC commands in Microsoft SQL Server and use it frequently in our day to day work for different purposes for maintenance, monitoring, verification and optimization like DBCC CHECKDB, DBCC SQLPERF(LOGSPACE), DBCC OPENTRAN, DBCC INPUTBUFFER, DBCC DBREINDEX, DBCC UPDATEUSAGE etc. DBCC expanded form is “Database Console Command statements”. So far these DBCC statements can only be executed against SQL Server database engine, but there was no support for other services like Integration services, Reporting services or Analysis services. Starting with new release of Microsoft SQL Server 2016, Microsoft has started supporting new DBCC commands for SQL Server Analysis Services to perform different tasks. These DBCC commands are very different from regular DBCC commands which we are used to.

The new DBCC commands which are introduced in Microsoft SQL Server 2016 are useful for both tabular and multidimensional SSAS databases and is used for checking for consistency and corruption of the databases. In tabular mode DBCC commands checks for corruption in any objects, segments, statistics, compression, dictionaries, and column stats and in multidimensional mode the DBCC looks for issues with indexes and statistics and also for validating metadata and checks out for physical data corruption. These DBCC commands for Analysis Services can be executed from SQL Server Management Studio.

The permissions required to run DBCC commands against Analysis Services is Admin permissions on Analysis Service instance. To run the DBCC commands for Analysis Service, we need to launch SQL Server 2016 Management Studio and then need to connect to Analysis Service instance. As of now these DBCC commands can be executed only against SQL Server 2016 analysis service instance, but not on any of the older instances. Typical DBCC commands to check consistency of analysis service database cube looks like below. You can get the DatabaseID and CubeID from the properties of analysis database and properties of cube and other properties from respective sections on which we want to run the checks against.

<DBCC xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
	<Object>
		<DatabaseID></DatabaseID>
		<CubeID></CubeID>
		<MeasureGroupID></MeasureGroupID>
		<PartitionID></PartitionID>
	</Object>
</DBCC>

If the above command completes successfully, then the result will show empty XML results set. It is important to review the message tab in the results window for more information regarding what items were checked. The errors that are returned by the dbcc commands can be due to Table metadata corruption, Corruption in the storage layer, Corrupt table statistics, Corrupt partition segment, Missing system table, etc.

Hope this was helpful.

This is applicable for below versions of SQL Server

SQL Server 2016

Thanks,
SQLServerF1 Team
In-Depth Blogs on SQL Server, Information about SQL Server Conferences and Events, Frequently asked questions, SQL Server Trainings