Database Consistency Checker DBCC CHECK For Analysis Services Database in SQL Server 2016 for Tabular and Multidimensional Databases

In SQL Server 2016 you will see a new feature in Analysis Services to perform Database Consistency Checker (DBCC Check) to find corruption issue against Analysis Services Database or in Individual Objects. The Database Consistency Checker (DBCC) consists of a single XMLA command, DBCC, which checks for corruption across the database or on individual objects. The command takes an object definition and returns either an empty result set or detailed error information if the object is corrupted. In this article we will learn How to Identify Analysis Services Database Corruption in SQL Server 2016.

Learn How to Identify Analysis Services Database Corruption in SQL Server 2016

For multidimensional databases in SQL Server 2016 Analysis Services, one can run DBCC Check for Index Corruption. However, Tabular databases are subject to a wider range of consistency checks like objects, dictionaries, column statistics, etc.

Detection of Tabular Database corruption includes:

• Check Objects
• Check Segment Statistics
• Check Dictionaries
• Check Column Statistics
• Validate Compression

Permissions Required to Run DBCC CHECK on Analysis Services Database

One must be Full Control Permission for an Analysis Services Database or you must be a Server Administrator (a member of the Server Role) to run DBCC XMLA command.

Trending Analysis Services Articles and Tips

• How to Detach SSAS Database in SQL Server
• How to Attach SSAS Database in SQL Server
• How to Synchronize Analysis Services Database Using Synchronize Database Wizard in SSMS
• How to Restore Analysis Services Database in SQL Server Using SQL Server Management Studio
• How to Automate Backup of Analysis Services Database Using SQL Server Agent Job
• How to Backup Analysis Services Database in SQL Server Using SQL Server Management Studio
• Database Consistency Checker DBCC CHECK For Analysis Services Database in SQL Server 2016 for Tabular and Multidimensional Databases

How to Run Analysis Services DBCC commands in Management Studio

Step 1: Connect to SQL Server 2016 Analysis Services instance using SQL Server Management Studio.

Step 2: In SSMS, Open a New XMLA Query window and copy paste the below sample code and make the changes as per your needs.

DBCC XMLA Command to Run DBCC CHECK against Analysis Service Database in Tabular or Multidimensional Databases

<DBCC xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
	<Object>
		<DatabaseID>AdventureWorksDW2014</DatabaseID>
		<CubeID>Adventure Works</CubeID>
	</Object>
</DBCC>

DBCC XMLA Command to Run DBCC CHECK against specific Analysis Service Database Object

<DBCC xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
	<Object>
		<DatabaseID>AdventureWorksDW2014</DatabaseID>
		<CubeID>Adventure Works</CubeID>
		<MeasureGroupID>Fact Internet Sales</MeasureGroupID>
		<PartitionID>Internet_Sales_2006</PartitionID>
	</Object>
</DBCC>

Sample Output of DBCC CHECK for Analysis Services Database

Within SSMS Results tab you will receive an empty result set if no problems were detected within the Analysis Service Object or a Database.

<return xmlns="urn:schemas-microsoft-com:xml-analysis">
	<root xmlns="urn:schemas-microsoft-com:xml-analysis:empty" />
</return>

However, in the Messages tab SSAS will provide you detail information even though it is not always reliable for a smaller database. Status messages are sometimes trimmed to indicate the command is completed. A typical example message report is mentioned below for your reference.

Messages reported from DBCC for the cube validation check

Executing the query ...
READS, 0
READ_KB, 0
WRITES, 0
WRITE_KB, 0
CPU_TIME_MS, 0
ROWS_SCANNED, 0
ROWS_RETURNED, 0

<DBCC xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
<Object>
<DatabaseID>AdventureWorksDW2014 </DatabaseID>
<CubeID>Adventure Works</CubeID>
</Object>
</DBCC>
Started checking segment indexes for the 'Internet_Sales_2012' partition.
Started checking segment indexes for the 'Internet_Sales_2013' partition.
Finished checking segment indexes for the 'Internet_Sales_2012' partition.
Started checking segment indexes for the 'Internet_Sales_2014' partition.
Finished checking segment indexes for the 'Internet_Sales_2014' partition.
Started checking segment indexes for the 'Internet_Orders_2012' partition.
Started checking segment indexes for the 'Internet_Orders_2013' partition.
Finished checking segment indexes for the 'Internet_Orders_2012' partition.
... 
Run complete

How to View Complete DBCC Output in Analysis Services in SQL Server 2016

One can view the complete DBCC output either by using SQL Server 2016 Profiler or Analysis Services xEvents.

DBCC XMLA command checks for physical data corruption, as well as logical data corruption that occur when orphaned members exist within a segment. However, it skips remote, empty, or unprocessed partitions. The DBCC XMLA command runs in a read transaction, and hence it can be kicked out by force commit timeout. However, Partition checks are run in Parallel.

Trace DBCC output in SQL Server Profiler 2016

In order to view DBCC output in a profiler trace that includes Progress Reports Event such as Process Report Begin, Progress Report Current, Progress Report End and Progress Report Error. Start the trace which includes above mentioned events in SQL Server 2016 Profiler and execute the XMLA script to run DBCC check in SSMS.