mssqltips logo

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

By:   |   Updated: 2013-09-03   |   Comments (3)   |   Related: More > Database Console Commands DBCCs

Problem

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. Armando Prato wrote a great, detailed tip on DBCC UPDATEUSAGE here.

 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)

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

If I were to look at a couple of these index ID's using the following queries I can see what has changed:

 SELECT id AS CorruptID, rowcnt, used, reserved, rows 
FROM Apollo32.dbo.sysindexes 
WHERE ID = 18099105 
ORDER BY ID
SELECT id AS HealthyID, rowcnt, used, reserved, rows 
FROM Apollo32_Fixed.dbo.sysindexes 
WHERE ID = 18099105 
ORDER BY ID


look at a couple of these index ID's using the following queries

You will notice the used column changed from 9 in the corrupt DB to 8 in the healthy DB. If we try another example, you will notice both the used and reserved columns changed for this particular index:

You will notice the used column changed from 9 in the corrupt DB to 8 in the healthy DB

These incorrect row counts are what lead to incorrect space usage reports.

Next Steps
  • Running DBCC UPDATEUSAGE and DBCC CHECKDB will create a lock on tables as it's running. I recommend running this during a maintenance window when users are not using the system heavily.
  • Learn more about DBCC UPDATEUSAGE from Armando Prato here.
  • MSSQLTips.com offers tons of Upgrade tips. Check them out here.


Last Updated: 2013-09-03


get scripts

next tip button



About the author
MSSQLTips author Brady Upton Brady Upton is a Database Administrator and SharePoint superstar in Nashville, TN.

View all my tips
Related Resources




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name
*Email
Email me updates

Signup for our newsletter

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.





Friday, June 05, 2015 - 12:33:53 PM - Sourav M Back To Top

 

Hi Brady,


Nice Post. Thanks for that.


But I have a question here.


When you are runniung the query..


SELECT id AS HealthyID, rowcnt, used, reserved, rows 

FROM Apollo32_Fixed.dbo.sysindexes 

WHERE ID = 18099105 

ORDER BY ID

 

 

why the multiple records are coming for the same table? Is this based on no of indexes it has in the table?? I wanted to check how would we know which index is corrupted as here you highlighted that records count =9 was found top be corrupted. How did you find that and confirm thats the corrupted one where there are other records also came in the results.

 

Thanks


Monday, May 18, 2015 - 6:54:49 AM - Sameer Navare Back To Top

Ideally when should I run DBCC UPDATEUSAGE command?

Will it harm in any way for currently logged in users?  Are there any known impacts (bad) of executing this command on a table/database?

 

Please let me know

 

Sameer


Tuesday, October 07, 2014 - 2:14:09 AM - Tino Back To Top

We've upgraded about 10 databases from SQL2000 an where very afraid about the error messages.

DBCC UPDATESTATISTIC fixed it all - thank you for this explaination!

 



download

























get free sql tips

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



Learn more about SQL Server tools