Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

DBCC UPDATEUSAGE Expand / Collapse
Author
Message
Posted Monday, January 7, 2013 9:59 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, December 5, 2014 12:19 PM
Points: 66, Visits: 596
DBCC CheckDB job failed last night. This is SQL Server 2005 Enterprise Edition 64 bit environment. The message / solution indicated running "DBCC UPDATEUSAGE".

This is a first for me, should the command be run against the database? Or against the table listed? I'm assuming this should be run during a maintenance window.

Error message is as follows:

Progress: 2013-01-06 12:00:05.17 Source: Execute T-SQL Statement Task
Executing query "USE [xxx] ".: 50% complete End Progress

Error: 2013-01-06 12:01:21.03 Code: 0xC002F210 Source: Execute T-SQL Statement Task Execute SQL Task

Description: Executing the query "DBCC CHECKDB WITH ALL_ERRORMSGS" failed with the following error: "The In-row data USED page count for object "Project_SubFees",
index ID 9, partition ID 2544946730696704, alloc unit ID 2544946730696704 (type In-row data) is incorrect. Run DBCC UPDATEUSAGE. The In-row data RSVD page count for object "Project_SubFees", index ID 9, partition...The package execution fa... The step failed.

Any comments would be appreciated - thank you.
Post #1403724
Posted Monday, January 7, 2013 10:33 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 9:13 AM
Points: 40,609, Visits: 37,070
Just run exactly what the error says - DBCC UPDATEUSAGE. Doesn't usually take that long


Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1403746
Posted Monday, January 7, 2013 12:32 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, December 3, 2014 10:05 AM
Points: 199, Visits: 739
"take long" is quite relative. In the enviroment I know we lose data if a table is locked for more than 30 seconds. As far as I have seen the UPDATE-USAGE-command places a lot of locks and on tables with some million records the command runs much longer than 30 seconds.

Maybe - and I really mean MAYBE - you can solve the problem by rebuilding the indexe mentioned in the error message. Depending on the SQLServer-version you use you can do an online rebuild which should hold only minimal locks. I don't believe an index reorg will work but it would be another try.
Post #1403814
Posted Monday, January 7, 2013 12:44 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 9:13 AM
Points: 40,609, Visits: 37,070
WolfgangE (1/7/2013)
Maybe - and I really mean MAYBE - you can solve the problem by rebuilding the indexe mentioned in the error message.


Dropping and recreating should solve this particular corruption.



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1403822
Posted Tuesday, January 8, 2013 8:36 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, December 5, 2014 12:19 PM
Points: 66, Visits: 596
I ran the DBCC UPDATEUSAGE command without incident. The DBCC CheckDB runs fine. I don't have any experience dropping and recreating an index. What I've done is take a backup of the database (with the corruption) and will attempt this in a test environment. Learning process continues...

Thank you both for responding.
Post #1404285
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse