October 20, 2010 at 1:06 am
Hi,
Do we need still use DBCC UPDATEUSAGE in SQL Server 2008?
Thanks
October 20, 2010 at 2:18 am
No, typically not.
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
August 16, 2011 at 10:17 am
Interesting....
If I get this message.
Msg 2508, Level 16, State 3, Line 1
The In-row data RSVD page count for object "WM_PlannedSupplySummary", index ID 0, partition ID 118081026523136, alloc unit ID 118081026523136 (type In-row data) is incorrect. Run DBCC UPDATEUSAGE.
I have several of these messages in this database.
Any thoughts as to what the correct thing to do is?
Using MSSQL 2008 R2
tia
-Todd
August 16, 2011 at 10:44 am
Todd Canedy-416047 (8/16/2011)
Any thoughts as to what the correct thing to do is?
Msg 2508, Level 16, State 3, Line 1
The In-row data RSVD page count for object "WM_PlannedSupplySummary", index ID 0, partition ID 118081026523136, alloc unit ID 118081026523136 (type In-row data) is incorrect. Run DBCC UPDATEUSAGE.
I said that typically you don't need it. You have an error that is explicitly telling you to run it. (Guessing a DB upgraded from SQL 2000)
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
August 16, 2011 at 11:20 am
GilaMonster (8/16/2011)
Todd Canedy-416047 (8/16/2011)
Any thoughts as to what the correct thing to do is?Msg 2508, Level 16, State 3, Line 1
The In-row data RSVD page count for object "WM_PlannedSupplySummary", index ID 0, partition ID 118081026523136, alloc unit ID 118081026523136 (type In-row data) is incorrect. Run DBCC UPDATEUSAGE.
I said that typically you don't need it. You have an error that is explicitly telling you to run it. (Guessing a DB upgraded from SQL 2000)
Your assumption is correct.
Thank you for your prompt response.
August 17, 2011 at 8:23 am
ANY DB restored from SQL 2000 to 2005 or 2008 it is suggested that you run DBCC UPDATEUSAGE on all tables in the user dbs.
August 17, 2011 at 8:46 am
It depends why you might want to run dbcc updateusage. If when you run dbcc updateusage lots of internal values are updated then maybe it's useful.
The tricky thing with many of these types of questions is that it's very difficult to test. e.g. I look after a worldwide app so there's no quiet time. when the US is busy ( the servers are in the UK ) I might be tempted to update stats just before the bulk of US users come on line - I might also sometimes consider running dbcc updateusage. Index rebuilds ( selective ) occur around 06:00 uk time and stats updates around 01:00 uk time. The problem I have is that I can't undo a command and rerun a days business to see what would happen if I did or didn't run a command- I've tried using replay traces but it was inconclusive.
so in some ways it may be a comfort ( or a legacy action if you started with sql server many versions ago ) but if you think it might improve/assist or otherwise enhance your system or just put your mind at rest then run the check.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
August 17, 2011 at 9:18 am
DBCC UpdateUsage fixes page space information, nothing else. There were bugs in the the page space tracking algorithms on SQL 2000 and before, hence it was necessary to run UpdateUsage from time to time and hence it's recommended when upgrading a DB from SQL 2000. The bugs were supposedly fixed in SQL 2005 RTM but some persisted until SP3
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
August 17, 2011 at 11:21 am
GilaMonster (8/17/2011)
DBCC UpdateUsage fixes page space information, nothing else. There were bugs in the the page space tracking algorithms on SQL 2000 and before, hence it was necessary to run UpdateUsage from time to time and hence it's recommended when upgrading a DB from SQL 2000. The bugs were supposedly fixed in SQL 2005 RTM but some persisted until SP3
Even on a SQL 2008 R2 RTM compatibility level 100 database, I've seen DBCC UPDATEUSAGE result in minor changes (a few tens of pages here or there), but nothing like the massive changes it regularly made on SQL 2000.
August 17, 2011 at 11:57 am
You should not need updateusage on a 2005 sp3+/2008/2008 R2 instance. The page space bugs are supposed to all be fixed. If you encounter one (not an upgrade from pre 2005 SP3) then it should be reported to CSS as a bug.
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
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply