Tag: DBCC CHECKDB

Type In-row data is incorrect. Run DBCC UPDATEUSAGE.

The Issue:

Integrity Check (DBCC CHECKDB) produces the error

The In-row data USED page count for object “my_table”, index ID 9, partition ID 2609137898356736, alloc unit ID 2609137898356736 (type In-row data) is incorrect. Run DBCC UPDATEUSAGE.

CHECKDB found 0 allocation errors and 2 consistency errors in table ‘my_table’ (object ID 1157579162).

CHECKDB found 0 allocation errors and 2 consistency errors in database ‘my_database’.
Outcome: Failed

BACKGROUND:

Database was just moved/upgraded from SQL Server 2000. SQL Server 2000 used to update the page space used metadata. After SQL Server 2005 the part of the migration/upgrade plan was to run DBCC UPDATEUSAGE. If you didn’t the error will appear when you run DBCC CHECKDB.

The output message above tell us that there is 1 consistency error in the database “my_database” and is a logical inconsistency. The error indicates that there are pages and row counts inaccuracies for that table and this type of errors can be fixed by running DBCC UPDATEUSAGE command.

HOW TO FIX:

Execute the DBCC UPDATEUSAGE(my_database) command in the context of your database you will see the following message indicating that were fixed the inaccuracies.

DBCC UPDATEUSAGE: Usage counts updated for table ‘my_table’ (index ‘my_table’ , partition 1):        RSVD pages (In-row Data): changed from … to … pages.DBCC execution completed. If DBCC printed error messages, contact your system administrator

 

After the page counts have been corrected your SQL Server will be back to normal. You can run DBCC CHECKDB after and it should not return any error messages.

 

Database integrity check

What is database integrity check?

In general the database integrity task includes checking the allocation and structural integrity of all the objects in the specified database. It can contain checking the database indexes.

It’s a good practice to run database integrity check on a set schedule, however there are some occasions when you will need to run check in some other times eg after hardware failure, electrical surge, the server unexpected shut down.

SQL Server provides number of commands to check the logical and physical integrity of all the objects in the specified database:

 

Command

Description

Command Level

Usage

DBCC CHECKDB Check for consistency errors, validates the integrity of everything in a database Database level Run every day during low usage times
DBCC CHECKALLOC Checks the consistency of disk space allocation structures for a specified database Database level DBCC CHECKDB is a superset of DBCC CHECKALLOC (includes allocation checks in addition to checks of index structure and data integrity), DBCC CHECKTABLE (for all tables in the database) and DBCC CHECKCATALOG.

 

DBCC CHECKTABLE Checks the integrity of all the pages and structures that make up the table or indexed view. Including pages linkage, sort order, pointers, page offsets Run for table and view in the database
DBCC CHECKCATALOG Checks for catalog consistency within the specified database. Checks that every data type in syscolumns has a matching entry in systypes and that every table and view in sysobjects has at least one column in syscolumn Runs on the database

 

NOTE: Do integrity check for all system databases such as master, model, msdb, distribution. The TEMPDB database does not need an integrity check, as it gets created from scratch at each SQL Server service restart.

 

For the majority of my SQL Servers I use Ola database maintenance solution see more details here http://ola.hallengren.com/sql-server-integrity-check.html

The command I execute looks similar to below

EXECUTE dbo.DatabaseIntegrityCheck
@Databases = 'USER_DATABASES',
@CheckCommands = 'CHECKDB'

 

What you do if you find CHECKDB error?

The answer is get on to it as soon as possible.

  1. Try to restore your database from good backup
  2. In case if no good backup available use REPAIR OPTION with DBCC CHECKDB. Only do this if you can’t find good backup as repair option may lead to the data loss

 

So what are the REPAIR OPTIONS?

  • REPAIR_FAST – does minor fast repairs on database without risk of data loss such as repairing extra keys in nonclustered indexes
  • REPAIR_REBUILD – does minor repairs and also rebuilds indexes without risk of data loss
  • REPAIR_ALLOW_DATA_LOSS – this is a time consuming process and can result in some data loss. It includes allocation and deallocation of rows and pages for correcting allocation errors, structural row or page errors, and deletion of corrupted text objects. Back up the database after repairs are completed

Note: All of these repair options require the database to be in single user mode. Hence downtime is necessary.

Use the following command

 

ALTER DATABASE AdventureWorks2008R2 SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

BEGIN TRANSACTION;

  DBCC CHECKDB ('AdventureWorks2008R2',  REPAIR_ALLOW_DATA_LOSS);

  ALTER DATABASE AdventureWorks2008R2 SET MULTI_USER;

COMMIT TRANSACTION;
GO