다음을 통해 공유

SSAS Restore Cube Error: "Database files failed to pass consistency checks"

  • 아티클

Applies to: SQL Server Analysis Services | Tabular model databases only

When restoring a tabular model, Analysis Services performs a database consistency check (DBCC) before the final commit. If the database is corrupted, or there is some other problem with the model, restore fails with this error: "Database files failed to pass consistency checks". Try the following approaches to resolve this error.

Approach 1: Apply the latest service update and retry restore

Recent software updates include fixes to DBCC functionality.  Apply the latest service packs or cumulative updates if you are running the original product release of SQL Server 2012.  Updating the server to the latest version might be sufficient for resolving the error. If you are using multiple servers to backup and restore your databases, be sure that all servers are running the same version and cumulative update.

Approach 2: Bypass the database consistency check and retry restore

Possibly, the DBCC is catching a model inconsistency that raises an error, but is not actually database corruption. A second approach to resolving the error includes temporarily disabling the DBCC while you restore the tabular model database. Once the database is restored, thoroughly test the model to ensure it is in a valid state. To disable DBCC, manually add DisableConsistencyChecks to the msmdsrv.ini file of the tabular instance.

Warning: DisableConsistencyChecks does not exist in the msmdsrv.ini file by default. You must add it manually, and use it only for diagnostic testing. This setting is not documented in Books Online and its behavior may change from release to release without prior notification.

  1. If possible, use a test server that is not running production workloads.
  2. Find the msmdsrv.ini file that belongs to the tabular instance on which you are attempting to restore the database. This setting only applies to tabular models.
  3. Open the msmdsrv.ini file in a text editor.
  4. Find the VertiPaq section at the end of the file. Paste <DisableConsistencyChecks>1</DisableConsistencyChecks> into the section. Valid values are 1, 0, and -1. Use 1 to disable consistency checks. Use 0 to enable consistency checks. For -1, use the instructions provided further down this page.
  5. Save the file. Restart the service. Retry database restore.

The following example shows how the VertiPaq section of msmdsrv.ini should look after you add the setting.

<VertiPaq>
   <DisableConsistencyChecks>1</DisableConsistencyChecks>
   <ThreadPool>
      <CPUs>0</CPUs>
      <GroupAffinity/>
   </ThreadPool>
   <DefaultSegmentRowCount>0</DefaultSegmentRowCount>
   <ProcessingTimeboxSecPerMRow>-1</ProcessingTimeboxSecPerMRow>
   <SEQueryRegistry>
 <Size>512</Size>
 <MinKCycles>0</MinKCycles>
 <MinCyclesPerRow>0</MinCyclesPerRow>
 <MaxArbShpSize>16384</MaxArbShpSize>
   </SEQueryRegistry>
   <Log>
      <ErrorLog>
 <LogErrorTypes>
  <KeyNotFound>0</KeyNotFound>
  <KeyDuplicate>2</KeyDuplicate>
  <NullKeyNotAllowed>2</NullKeyNotAllowed>
 </LogErrorTypes>
      </ErrorLog>
   </Log>
</VertiPaq>

Approach 3: Check for database corruption at each commit and fix the problem

A third approach, which is the most conservative, is to check for database corruption and then fix the problem that introduces corruption into your database. Although the error occurs on restore (because that is when the consistency check is performed), database corruption is probably happening earlier, while populating the database.

  1. To check for database corruption, set DisableConsistencyChecks to -1. This forces the DBCC to be performed as part of every transactional commit operation
  2. Restart the service.
  3. Process the database as you would normally.
  4. Monitor processing in SQL Server Profiler to determine where the error occurs. The error will be raised prior to the commit.

Warning: Setting DisableConsistencyChecks to -1 has a negative impact on performance, so if possible, use a test server that is not running production workloads.

Approach 4: Redeploy the solution

For relatively small tabular databases, you might want to process and redeploy the solution from SQL Server Data Tools (SSDT).  If this approach is unreasonable due to database size or other restrictions, you should try the previously mentioned approaches, perhaps engaging Microsoft Customer Support for assistance in diagnosing the inconsistency.

Thanks to Akshai Mirchandani, Rock Wang, and Russell Pitts for their assistance with this article.