May 18, 2009 at 11:10 am
Hi,
Iam running weekly job database integrity check, created using Maintenance plan in sql sevrer 2005. Every time Iam getting the following error for one database
failed with the following error: "The In-row data RSVD page count for object "WCLSSTATL", index ID 0, partition ID 96295478820864, alloc unit ID 96295478820864 (type In-row data) is incorrect. Run DBCC UPDATEUSAGE.
If run DBCC UPDATEUSAGE, its giving me NO errors. After running DBCC UPDATEUSAGE, I ran the DBCC CHECKDB again I got NO errors.
But next week, Iam getting the same error again.
What will be permanent fix for this??
May 18, 2009 at 11:51 am
DBCC updateusage will corrects the rows, used pages, reserved pages, leaf pages and data page counts for each partition in a table or index.
Better to check fragmentation and rebuild or reorganize the index on that perticular table.
This will be a common issue/routine issue due to heavy DDL Usage
May 18, 2009 at 12:33 pm
No - it's got nothing to do fragmentation.
On 2000, the row/page counts could become incorrect (and even negative) because of bugs in the algorithms to maintain them during heavy DML operations. In the release notes and readme of 2005 RTM (in fact in BOL for DBCC too) it explains about the need to run DBCC UPDATEUSAGE after the upgrade to correct this problem, and how the algorithms were fixed in 2005.
Unfortunately, there was still a bug in the algorithm. The latest builds of 2005 (SP3 onwards I believe) have the bug fixed and so you shouldn't see issues like this. If you do see them after upgrading past SP3, it is a SQL Server bug and you should call Product Support to investigate.
Thanks
Paul Randal
CEO, SQLskills.com: Check out SQLskills online training!
Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005
May 18, 2009 at 12:59 pm
thanks Paul,
We recently upgraded from sql server 2000 to sql server 2005 and applied SP3.Before upgrade, Iam not sure we were getting this error or not. But after the upgrade every week during the maintenace plan job Integrity check, We are getting the error.Once I ran update usage and then dbcc checkdb no error.
So, if this is the case, should we go for MS Support or any alternate fix for this?
May 18, 2009 at 1:54 pm
The error was probably a left-over from SQL 2000. If you get it again, after you've fixed it with UpdateUsage, then maybe consider calling product support.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 18, 2009 at 1:59 pm
I didn't add the check until 2005 -all explained in BOL and the readme.
Only need to call PSS if it happens again.
Paul Randal
CEO, SQLskills.com: Check out SQLskills online training!
Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005
May 18, 2009 at 3:00 pm
We still have the Compatibility mode as 80 for the user databases. In this case, the error can be ignored? or I must need to burn a call to MS
May 18, 2009 at 3:48 pm
It's got nothing to do with compat mode.
If you're seeing repeated cases where you run UpdateUsage, the error goes away and then later comes back, and you're on SQL 2005 SP3 or higher, then call CSS and log a case.
What does SELECT @@Version return?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 18, 2009 at 4:32 pm
I got the following idea..
I will create a job instead of the maintenance plan for integrity check and add DBCC UPDATEUSAGE(Mydb) before DBCC CHECKDB()Mydb. Is that works or not?
May 18, 2009 at 4:43 pm
Select @@version:
Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86)
Nov 24 2008 13:01:59
Copyright (c) 1988-2005 Microsoft Corporation
Developer Edition on Windows NT 5.2 (Build 3790: Service Pack 2)
(1 row(s) affected)
May 18, 2009 at 5:19 pm
No - don't add DBCC UPDATEUSAGE to your maintenance plan. It should be unnecessary. If you find that you're getting those errors from DBCC CHECKDB then its a *BUG* and you need to call Product Support.
Paul Randal
CEO, SQLskills.com: Check out SQLskills online training!
Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005
May 22, 2009 at 1:35 pm
Hi,
I have created a job to check the integrity of the database(which is giving error, weekly) and scheduled to run daily as
DBBCC CHECKDB (DB_NAME) WITH NO_INFOMSGS
Now, since from last 3 days I did not get any error.
So, If run DBCC CHECKDB weekly Iam getting the error and if run daily Iam not getting the error
So what can we conclude from the above results???
or do I need to wait at least 1 week?
May 22, 2009 at 2:26 pm
That whatever is causing the problem has a periodicity of more than 3 days. You need to wait to see if it happens again.
Paul Randal
CEO, SQLskills.com: Check out SQLskills online training!
Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005
June 1, 2009 at 2:15 pm
Hi Paul,
I got the error again after 10 days this time. Could you please suggest me what action should we take?
Before upgrade, I did not see this error. After upgrade to sql server 2005, Iam seeing this error.
Well, While upgrading I performed all the steps
dbcc updateusage
dbcc checkdb (dbname) with datapurity
dbcc checkdb (dbname) with no_infomsgs
sp_updatestats
everything went well. But now the error coming up...is this due upgrade to 2005?
June 1, 2009 at 2:57 pm
Call Product Support - it's a bug.
Paul Randal
CEO, SQLskills.com: Check out SQLskills online training!
Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply