SQL Server Articles, Tips, FAQs, Reviews, Whitepaper, News etc - MyTechMantra.com

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 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.

Important: You need to note that Analysis Services DBCC messages are not reported to the Windows Application Event Log or the msmdsrv.log file.

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.

Recommendation: A restart of SQL Server Analysis Service might be required to pick up any corruption errors that have occurred since the last Analysis Service restart. As reconnecting to the server is not enough to pick up the changes.

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 BeginProgress Report CurrentProgress 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.

Chetna Bhalla

Chetna Bhalla

LESS ME MORE WE

Chetna Bhalla, the founder of MyTechMantra.com, believes that by sharing knowledge and building communities, we can make this world a better place to live in. Chetna is a Graduate in Social Sciences and a Masters in Human Resources and International Business. She is an alumnus of Vignana Jyothi Institute of Management, Hyderabad, India. After graduation, Chetna founded this website, which has since then become quite a favorite in the tech world. Her vision is to make this website the favorite place for seeking information on Databases and other Information Technology areas. She believes that companies which can organize and deploy their data to frame strategies are going to have a competitive edge over others. Her interest areas include Microsoft SQL Server and overall Database Management. Apart from her work, Chetna enjoys spending time with her friends, painting, gardening, playing the violin, and spending time with her son.

Add comment

Newsletter Signup! Join 15,000+ Professionals




Be Social! Like & Follow Us...

Follow us

Don't be shy, get in touch. We love meeting interesting people and making new friends.

Recent SQL Server Tips

Manning