SQL Server DBCC CHECKDB Overview

By bradyupton in Maintenance 0 Comment

SQL Server database corruption can be a problem and can cause serious damage to a database. If you’re an experienced DBA then you probably have safeguards in place to detect this, but over the years I’ve seen hundreds of SQL Servers with no detection methods at all and this is a problem. There are a few ways to detect database corruption, but this tip will focus more on DBCC CHECKDB.

You may or may not have heard of DBCC (database console commands) statements. These statements are used to perform different operations in your database and can be broken down into four categories: Maintenance, Miscellaneous, Informational, and Validation. I use some of the DBCC statements on a daily basis, but none more than DBCC CHECKDB.

What is SQL Server DBCC CHECKDB

DBCC CHECKDB, from Microsoft MSDN Library, checks logical and physical integrity of all the objects in the specified database by performing the following operations:

  • Runs DBCC CHECKALLOC on the database – Checks consistency of disk space allocation structures for a specified database.
  • Runs DBCC CHECKTABLE on every table and view in the database – Checks the integrity of all the pages and structures that make up the table or indexed view.
  • Runs DBCC CHECKCATALOG on the database – Checks for catalog consistency within the database.
  • Validates the contents of every indexed view in the database.
  • Validates link-level consistency between table metadata and file system directories and files when storing varbinary(max) data in the file system using FILESTREAM.
  • Validates the Service Broker data in the database

If you’ve ever ran DBCC CHECKDB you know it takes some time for large databases. Now that you know all of the steps that are run, you can see why it takes time to complete.

How can SQL Server DBCC CHECKDB help me?

Data corruption is bad. It can cause all sorts of issues within the database that may include incorrect data results, failed SQL statements, and in some cases can take down the entire SQL instance. DBCC CHECKDB warns you of corruption so that you can fix it before (hopefully) it gets too bad.

How do I use SQL Server DBCC CHECKDB?

DBCC CHECKDB is pretty straightforward. There are a few options you can use with the statement and we’ll go over some of those in the next section, but the basic syntax looks like this:

DBCC CHECKDB ('DatabaseName')

Pretty simple.

Automate SQL Server DBCC CHECKDB

Obviously, you don’t want to log in every morning and run this statement on each database, so you can automate this process using a few different methods:

  • SQL Server Maintenance plans – Maintenance plans are part of SQL Server out of the box (unless you’re running Express Edition). I don’t like using maintenance plans for the most part, but I don’t mind using them for this type of task. In the Maintenance Plan toolbox you’ll need to use the Check Database Integrity task. The only configurable option is to include indexes so it’s not really user friendly, but in some cases this is all you need. Again, we’ll talk about other options in the next section.

Custom scripts – Custom scripts are usually what I use and offer the best flexibility as far as adding the options you want. My go-to scripts are already created and free to use from Ola Hallengren. He’s done a wonderful job of creating these and sharing them to the world. Thanks Ola!

Click here to view the rest of this post.

Fixing SQL Server error in-row data RSVD page count is incorrect

By bradyupton in Monitoring 0 Comment

I ran DBCC CHECKDB (Integrity Checks) in my maintenance plan and I also ran it manually, but I keep getting the error “The In-row data RSVD page count for object “table_name” , index ID 0, partition ID 58037252456448, alloc unit ID 58037252456448 (type In-row data) is incorrect.” How can I fix this error?

Solution

Before we discuss the simple steps to fixing this error, let’s discuss what might have happened in this situation.

Anytime you see an integrity check error it might make you a little queasy. No need to worry on this error. If you have recently upgraded from SQL Server 2000 then this error is somewhat common. I know what you’re saying, “Why is there a tip regarding SQL Server 2000?” Trust me, a lot of people are still running SQL Server 2000 which is now out of compliance with Microsoft, so everyone is in a rush to upgrade. Anyway, SQL Server 2000 used to update the page space used metadata. Once SQL Server 2005 came along, this was no longer the case so if you didn’t run DBCC UPDATEUSAGE after the upgrade/migration this error is likely to appear when you run DBCC CHECKDB.

Here is what the error message looks like:

Msg 2508, Level 16, State 3, Line 1

The In-row data RSVD page count for object "table_name", index ID 0, partition ID 58037252456448,
alloc unit ID 58037252456448 (type In-row data) is incorrect. Run DBCC UPDATEUSAGE.

Running DBCC UPDATEUSAGE

If you notice in the maintenance plan or the DBCC CHECKDB results above, it displays “Run DBCC UPDATEUSAGE” after the error message. Ta da! After backing up your databases and establishing a maintenance window run DBCC UPDATEUSAGE (databasename). In a perfect world, SQL would notice this error and run DBCC UPDATEUSAGE on the appropriate table. This is not a perfect world, so we have to run it manually.

DBCC UPDATEUSAGE reports and corrects pages and row count inaccuracies in the catalog views. These inaccuracies may cause incorrect space usage reports returned by the sp_spaceused system stored procedure – MSDN.

 DBCC UPDATEUSAGE (Apollo32_Fixed)

After running DBCC UPDATEUSAGE (databasename) on my questionable database you can see that all the page counts have been updated:


DBCC UPDATEUSAGE: Usage counts updated for table 'UserList' (index 'UserList', partition 1):
        USED pages (In-row Data): changed from (5) to (4) pages.
DBCC UPDATEUSAGE: Usage counts updated for table 'UserRights' (index 'UserRights', partition 1):
        USED pages (In-row Data): changed from (72) to (24) pages.
        RSVD pages (In-row Data): changed from (80) to (33) pages.
DBCC UPDATEUSAGE: Usage counts updated for table 'UserRights' (index 'UserRights', partition 1):
        USED pages (In-row Data): changed from (5) to (4) pages.
DBCC UPDATEUSAGE: Usage counts updated for table 'UserGroups' (index 'UserGroups', partition 1):
        USED pages (In-row Data): changed from (4) to (3) pages.
DBCC UPDATEUSAGE: Usage counts updated for table 'Application' (index 'Application', partition 1):
        USED pages (In-row Data): changed from (3) to (2) pages.

You only need to run DBCC UPDATEUSAGE once. After the page counts have been corrected your SQL Server will be back to normal. If you do not fix this issue right away, page counts will continue to get worse when data is added and will result in abnormalities in space usage reports.

After the update, running DBCC CHECKDB gives me a clean database again:

CHECKDB found 0 allocation errors and 0 consistency errors in database ”database_name’. DBCC execution completed. If DBCC printed error messages, contact your system administrator.

In essence, DBCC UPDATEUSAGE updates the used, reserved, and rows columns of the sysindexes (later named sys.indexes in SQL Server 2005) table.

In this example I have two databases named Apollo32 and Apollo32_Fixed. When running an integrity check against Apollo32 I get the error described above. Apollo32_Fixed is a copy of Apollo32 after running DBCC UPDATEUSAGE.

Before and After Comparison

If I compare Apollo32.dbo.sysindexes to Apollo32_Fixed.dbo.sysindexes I can see I have 534 differences.

 (SELECT id, rowcnt, used, reserved, rows FROM Apollo32.dbo.sysindexes
EXCEPT
SELECT id, rowcnt, used, reserved, rows FROM Apollo32_Fixed.dbo.sysindexes)
UNION ALL
(SELECT id, rowcnt, used, reserved, rows FROM Apollo32_Fixed.dbo.sysindexes
EXCEPT
SELECT id, rowcnt, used, reserved, rows FROM Apollo32.dbo.sysindexes)

SQL Freelancer SQL Server RSVD fix
Click here to view the rest of this post.