Evaluate Weigh the pros and cons of technologies, products and projects you are considering.

Introducing DBCC CHECKDB for Analysis Services 2016

DBCC CHECKDB, a long-awaited feature for SQL Server Analysis Services, provides the ability to perform database consistency checks against tabular and multidimensional databases.

One new feature of SQL Server Analysis Services (SSAS) 2016 will be well received by DBAs and the SQL Server business intelligence developer community: the database consistency checker (DBCC) CHECKDB command. The command provides the ability to perform a database consistency check against tabular and multidimensional databases. DBCC CHECKDB consists of a single XMLA command that checks for corruption issues across the analysis database or on any individual object. The DBCC command takes an object definition and returns either an empty result set or a detailed error message if corruption is identified for an analysis service object.

To check for index corruption issues in an SSAS multidimensional database, you can execute the DBCC CHECKDB command to:

  • validate metadata;
  • scan segments to look for physical corruption issues; and
  • check segment statistics and indexes.

To check for index corruption issues in an SSAS tabular database, you can execute the DBCC CHECKDB command to:

Note that an SSAS server restart might be required to pick up any corruption issues that would have occurred within the analysis service database since the time the SSAS instance was last restarted. Just reconnecting to an analysis server instance will not be enough to pick up the changes or to identify new corruption issues if any exist. Hence, it is advisable to perform DBCC checks during the routine maintenance window.

You must be a server administrator (i.e., a member of the server role) to run the DBCC CHECKDB command on an SSAS database or object to identify corruption issues within a tabular or multidimensional database. Figure 1 provides an example of how to execute a DBCC command to run against an SSAS database.

  1. Open SQL Server Management Studio (SSMS) and connect to a SQL Server Analysis Services 2016 instance.
  2. Open a new XMLA query window, duplicate the sample code from Figure 1 to perform a DBCC check against the AdventureWorksDW analysis services sample database, which is a tabular or a multidimensional database.
DBCC check
Figure 1. Perform a DBCC check.

Figure 2 shows an example of how to create a DBCC command that runs against an SSAS database object. In the XMLA query window, duplicate the sample XMLA code from Figure 2 to run a DBCC command against an analysis services object within the analysis service database:

DBCC command
Figure 2. Run a DBCC command against an analysis service object in an analysis service database.

You can view the DBCC output by using SQL Server 2016 Profiler or Analysis Services xEvents. Note that DBCC error messages are not written to a Windows application log or to a MSMDSRV.LOG file.

DBCC checks against analysis services will look for physical and logical data corruption issues that generally occur when orphaned members exist within a segment. However, the DBCC command skips remote, unprocessed or empty partitions. The DBCC command runs in a read transaction mode and partition checks are run in parallel at the same time.

Figure 3 shows sample output generated by the successful execution of the DBCC CHECKDB command on the AdventureWorksDW database.

Figure 3. Output of a DBCC CHECKDB command.

In the results tab shown above, the DBCC command will show an empty result set if no corruption issues were detected within the analysis service object or in an analysis service database. Figure 4 shows the same output.

corruption issues
Figure 4. No corruption issues detected.

In the messages tab shown above, you will see detailed information even though it is not always reliable for smaller analysis services databases. Sometimes, status messages are trimmed to indicate that the DBCC command executed successfully. Figure 5 shows a sample result set. However, as noted above, it is highly recommended to execute the DBCC command after restarting a SSAS instance so it could pick up any corruption errors that could have occurred since the last restart of the SSAS instance.

cube validation check
Figure 5. Sample message from a cube validation check.

To trace a DBCC XMLA output, open SQL Server 2016 Profiler and start a new trace, which includes events such as progress report events (e.g., progress report begin, progress report current, progress report end, progress report error). Then execute the DBCC XMLA command in SSMS to see the DBCC output results in detail within SQL Server 2016 Profiler.

Next Steps

SQL Server must-have management tools

SQL Server Management Studio secrets

Restoring a corrupt database

Dig Deeper on XML test

Join the conversation


Send me notifications when other members comment.

Please create a username to comment.

How important to you is the DBCC CHECKDB command in SSAS 2016?
Hi Ashish,
Yes it is needed features, but I as knows Microsft did not provide a repair solution like for the OLTP database. I thisk the only way is to restore the last  valid backup.