August 20, 2010 at 9:10 am
Hi, previous articles I've read and forums I've watched or attended keep telling me there is no need to run dbcc updateusage on a 2008 DB anymore unless you have upgraded it from SQL2000.
I've recently installed SQL2008 SP1 on a windows 2008 Server. This is being used by the MS SCCM app which has created an SMS database (compat level 100). Having looked at an issue we have with the TaskSequence table I noticed that the amount of space allocated to index and unused was 0, whilst the data amount was 400mb. However the overall reserved space was only 10mb, how does that work!?
Anyway having gone through a process of copying the data out (6 rows!) to a copy table, deleting FK refs and then truncating etc. Once I copied the data back in the stats were looknig better...10mb reserved, 9 mb data, 1mb unused. After a while it went back to the original figures mentioned above!? So I thought I'd try a dbcc updateusage, and guess what, all the stats were correct again ie 10mb reserved, 9 mb data, 1mb unused. So presumably we DO still need to regularly run updateusage, yes??
August 20, 2010 at 1:35 pm
Microsoft best practice reads this
Do not run DBCC UPDATEUSAGE routinely for databases created in SQL Server 2005 or higher or on upgraded databases that have been corrected once by using DBCC UPDATEUSAGE. Because DBCC UPDATEUSAGE can take some time to run on large tables or databases, it should not be used only unless you suspect incorrect values are being returned by sp_spaceused
It says do not run routinely not never
Pradeep Adiga
Blog: sqldbadiaries.com
Twitter: @pradeepadiga
August 20, 2010 at 3:00 pm
simon.letts (8/20/2010)
Once I copied the data back in the stats were looknig better...10mb reserved, 9 mb data, 1mb unused. After a while it went back to the original figures mentioned above!? So I thought I'd try a dbcc updateusage, and guess what, all the stats were correct again ie 10mb reserved, 9 mb data, 1mb unused.
You shouldn't need to run it on SQL 2005+. The reason it was needed on 2000 was that the algorithms that kept track of page space usage was a little broken (make that very broken).
Most of those bugs were fixed in SQL 2005, with a couple more fixed in 2005 SP2 and 2005 SP3. If you're running into this problem on SQL 2008, it sounds like you've encountered a bug. Call customer support and open a case with them (after making sure you're on the latest patch and CU).
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 23, 2010 at 3:08 am
Thanks for you comments guys. I'll open a support call with MS as suggested.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply