DBCC UPDATEUSAGE

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server Newbies » DBCC UPDATEUSAGE

DBCC UPDATEUSAGE

Rate Topic

Display Mode

Topic Options

Author

Message

jralston88

Posted Monday, January 07, 2013 9:59 AM

SSC Rookie

Group: General Forum Members
Last Login: Today @ 11:41 AM
Points: 32, Visits: 299

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

GilaMonster

Posted Monday, January 07, 2013 10:33 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 3:28 PM
Points: 37,730, Visits: 29,996

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

WolfgangE

Posted Monday, January 07, 2013 12:32 PM

Valued Member

Group: General Forum Members
Last Login: Tuesday, May 14, 2013 2:56 AM
Points: 74, Visits: 419

"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

GilaMonster

Posted Monday, January 07, 2013 12:44 PM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 3:28 PM
Points: 37,730, Visits: 29,996

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

jralston88

Posted Tuesday, January 08, 2013 8:36 AM

SSC Rookie

Group: General Forum Members
Last Login: Today @ 11:41 AM
Points: 32, Visits: 299

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 »

Permissions