DBCC UPDATEUSAGE is safe? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

Data Corruption Issues

DBCC UPDATEUSAGE is safe?

Author

Topic

CanadaDBA

583 Posts

Posted - 2010-01-07 : 14:21:59

SQL Server version 9.0.4035

I have a maintenance plan which checks databases integrity every week and it fails. I have another maint. plan for Rebuild indexes and works fine.

There are 116 database failure out of 157 database in the plan's history log. Here is the log for one of the databases:

Executing the query "DBCC CHECKDB(N'W16', NOINDEX)" failed with the following error: "The In-row data RSVD page count for object "SY40100", index ID 0, partition ID 14181193482240, alloc unit ID 14181193482240 (type In-row data) is incorrect. Run DBCC UPDATEUSAGE.
The In-row data RSVD page count for object "GL00105", index ID 0, partition ID 33293666942976, alloc unit ID 33293666942976 (type In-row data) is incorrect. Run DBCC UPDATEUSAGE.
The In-row data RSVD page count for object "frl_acct_code", index ID 0, partition ID 89947675951104, alloc unit ID 89947675951104 (type In-row data) is incorrect. Run DBCC UPDATEUSAGE.
The In-row data RSVD page count for object "SY03400", index ID 0, partition ID 138141406068736, alloc unit ID 138141406068736 (type In-row data) is incorrect. Run DBCC UPDATEUSAGE.
.
.
DBCC results for 'SY40100'.
There are 3497 rows in 53 pages for object "SY40100".
CHECKDB found 0 allocation errors and 1 consistency errors in table 'SY40100' (object ID 216387840).
.
.
DBCC results for 'GL00105'.
There are 636 rows in 19 pages for object "GL00105".
CHECKDB found 0 allocation errors and 1 consistency errors in table 'GL00105' (object ID 508021041).
.
.
DBCC results for 'frl_acct_code'.
There are 636 rows in 20 pages for object "frl_acct_code".
CHECKDB found 0 allocation errors and 1 consistency errors in table 'frl_acct_code' (object ID 1372492614).
.
.
DBCC results for 'SY03400'.
There are 1273 rows in 107 pages for object "SY03400".
CHECKDB found 0 allocation errors and 1 consistency errors in table 'SY03400' (object ID 2107870576).
.
.
CHECKDB found 0 allocation errors and 4 consistency errors in database 'W16'.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

Some databases have 1 consistency error and some like the above example have more.

I have a duplicate server which production backups are being restored into it every night. Running DBCC CHECKDB(N'W16', NOINDEX) on it produced the same errors. Then I ran DBCC UPDATEUSAGE(0) and it fixed the issue for this database on that machine. Is it safe to run DBCC UPDATEUSAGE in production as well?

Canada DBA

shan
Yak Posting Veteran

84 Posts

Posted - 2010-01-07 : 16:37:59

If its sql 2005 or up, You need not run DBCC UPDATEUSAGE per BoL

-Shan

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2010-01-08 : 03:20:51

Yes, it's safe to run.

I'm guessing that DB was upgraded from SQL 2000. Most of the problems that updateusage fix occurred in SQL 2000.
Basically, SQL 2000 had some bugs in the code that updated the page space used metadata.
On SQL 2000, CheckDB fixed them silently, in 2005 it does not, hence leading to errors like this.

All of the space used metadata bugs were supposedly fixed in 2005 RTM, but there were a couple that still occurred right up to SP3.
If you're not on SP3, consider applying that.
If you are and these errors still develop once fixed, then you've hit a new bug and should call customer support for assistance.

--
Gail Shaw
SQL Server MVP

CanadaDBA

583 Posts

Posted - 2010-01-08 : 08:16:05

Your guess is correct. The databases were originally created in SQL 2000 and restored into our new SQL 2005 environment. Those databases which were created in SQL 2005 are fine and CHECKDB does not error on them.

Since I mentioned SQL Server version is 9.0.4035, means SP3 has been applied.

Thanks for the help!

Canada DBA

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2010-01-08 : 12:05:54

I can never remember exactly what version number corresponds with what service pack. Not over 3 versions of SQL.

Since it is an upgraded DB, just run it once and you should be fine from there on.

--
Gail Shaw
SQL Server MVP

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-01-08 : 12:49:06

You should be on a minimum build of 4262. 4035 is the base SP3 product and contains a critical security vulnerability that fixed in the October monthly patches. I'd recommend upgrading to 4262 or going to an even higher cumulative update package.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-01-08 : 12:50:00

quote:
Originally posted by GilaMonster

Yes, it's safe to run.

I'm guessing that DB was upgraded from SQL 2000. Most of the problems that updateusage fix occurred in SQL 2000.
Basically, SQL 2000 had some bugs in the code that updated the page space used metadata.
On SQL 2000, CheckDB fixed them silently, in 2005 it does not, hence leading to errors like this.

All of the space used metadata bugs were supposedly fixed in 2005 RTM, but there were a couple that still occurred right up to SP3.
If you're not on SP3, consider applying that.
If you are and these errors still develop once fixed, then you've hit a new bug and should call customer support for assistance.

And you call yourself a developer.

You need a fancier title.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2010-01-08 : 14:50:28

^ lol true.

by the way, i don't always remember all the sub-versions either. that's why i like this:

SELECT SERVERPROPERTY('productversion'), SERVERPROPERTY ('productlevel'), SERVERPROPERTY ('edition')

better than @@Version

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2010-01-08 : 15:50:44

quote:
Originally posted by tkizer

quote:
Originally posted by GilaMonster

Yes, it's safe to run.

I'm guessing that DB was upgraded from SQL 2000. Most of the problems that updateusage fix occurred in SQL 2000.
Basically, SQL 2000 had some bugs in the code that updated the page space used metadata.
On SQL 2000, CheckDB fixed them silently, in 2005 it does not, hence leading to errors like this.

All of the space used metadata bugs were supposedly fixed in 2005 RTM, but there were a couple that still occurred right up to SP3.
If you're not on SP3, consider applying that.
If you are and these errors still develop once fixed, then you've hit a new bug and should call customer support for assistance.

And you call yourself a developer. You need a fancier title.

At my previous company I signed off two high-profile design documents with the title 'Database Overlord'.

Fancy enough?

--
Gail Shaw
SQL Server MVP

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2010-01-08 : 15:51:53

Nice. I like it! Think I'll have to print up some business cards with that on it lol

debrah.h48
Starting Member

4 Posts

Posted - 2010-01-17 : 23:35:22

DBCC UPDATEUSAGE takes Shared lock on the table and data modifications to the tables will be stalled (blocked). Does stage and production has same database size and hardware? It also depends on how busy the server is at the time of running this command. So use your best estimate and if you can schedule it.

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2010-01-18 : 00:40:55

quote:
Originally posted by debrah.h48

DBCC UPDATEUSAGE takes Shared lock on the table and data modifications to the tables will be stalled (blocked). Does stage and production has same database size and hardware? It also depends on how busy the server is at the time of running this command. So use your best estimate and if you can schedule it.

Nice copy/paste. You should give credit to the original author you quoted. Ok, I'll do it for you: http://social.msdn.microsoft.com/Forums/en-US/sqldatabaseengine/thread/843706be-0388-4123-90c3-7da09cfd212a

This is not strictly true though. A shared lock may or may not block updates.

Kristen
Test

22859 Posts

Posted - 2010-01-18 : 03:04:20

"The databases were originally created in SQL 2000 and restored into our new SQL 2005 environment"

You may want to look at the post that I am collating on migrating to SQL 2008:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=138230

there is a link there to an earlier similar post on migrating to SQL 2005, but a lot of the more "considered" things that are discussed for SQL 2008 will apply to SQL 2005

jimmycjen
Starting Member

15 Posts

Posted - 2010-06-02 : 11:20:05

quote:
Originally posted by GilaMonster

Yes, it's safe to run.

I'm guessing that DB was upgraded from SQL 2000. Most of the problems that updateusage fix occurred in SQL 2000.
Basically, SQL 2000 had some bugs in the code that updated the page space used metadata.
On SQL 2000, CheckDB fixed them silently, in 2005 it does not, hence leading to errors like this.

All of the space used metadata bugs were supposedly fixed in 2005 RTM, but there were a couple that still occurred right up to SP3.
If you're not on SP3, consider applying that.
If you are and these errors still develop once fixed, then you've hit a new bug and should call customer support for assistance.

--
Gail Shaw
SQL Server MVP

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources