Scary Saturday – DBCC UPDATEUSAGE

Signing In – 9.00 AM EDT


As i always say, be always prepared for the Worst as a DBA. Had to wake up at 6.30 AM, DR exercise was scheduled to begin at 7.00 AM and i was preparing for it ūüė¶
Distraction – Got an Email with Message ” Consistency Checks Job Failed” from one of my other Servers. Aaaaah…what a start it was:( ¬†Time not to PANIC!!…Well, time to prepare mentally. Prepared my Cup of Tea and had a phone conversation with one of my Friends which of course kicks out some stress from Ur poor brain especially when it’s early in the Morning!
Once my DR SQL Server is Up and running and all the Databases are Online(FYI, we’ve SRDF as our Disaster recovery Strategy for this Server/Application – It’s a dedicated Box), gone to the Consistency Checks Job for Error Messages..Interestingly it was yelling “Msg 2508, Level 16, State 3, Line 1
The In-row data RSVD page count for object “XXXXX”, index ID 0, partition ID 44405715894272, alloc unit ID 44405715894272 (type In-row data) is incorrect. Run DBCC UPDATEUSAGE.
CHECKDB found 0 allocation errors and 1 consistency errors in table ‘XXXXX’ (object ID 677577452).
CHECKDB found 0 allocation errors and 1 consistency errors in database ‘XYZ’.”



My first reaction after looking at this Error Msg was РThank God! Well, why would i thank god if my consistency checks Job failed! Coz, this is not a threatening condition of your Database and i was expecting more worse.
So what is this Error all about?
Well,SQL Server 2000 used to update the page space used metadata which is no more entertained starting SQL Server 2005 RTM. If your Database has been migrated from SQL Server 2000 you might not see this error until you run CHECKDB on that database for the first time. Running DBCC CHECKDB wont fix this Issue(in our case we are running on SQL Server 2008 R2 RTM x64), you’ve to run DBCC UPDATEUSAGE as per the message CHECKDB returned.

Now i remember one of my¬†colleague restoring a SQL 2000 Database on this new Server. Well, he forgot to run DBCC CHECKDB WITH DATA_PURITY once he migrated(Upgraded) Database to new Server. Ummm…again remembering myself ¬†“Well this is not dream where you’ll be only the one who touches a given SQL Server”
UPDATEUSAGE Results:
I’m truncating the message and changed the User Object names.
DBCC UPDATEUSAGE: Usage counts updated for table ‘DDD’ (index ‘XXXXLINK’, partition 1):
USED pages (In-row Data): changed from (1358) to (1354) pages.
RSVD pages (In-row Data): changed from (2726) to (1369) pages.
DBCC UPDATEUSAGE: Usage counts updated for table ‘DDD’ (index ‘XYZXUSER’, partition 1):
RSVD pages (In-row Data): changed from (2312) to (2313) pages.
DBCC UPDATEUSAGE: Usage counts updated for table ‘DDD’ (index ‘XACCNO’, partition 1):
USED pages (In-row Data): changed from (1580) to (1576) pages.
RSVD pages (In-row Data): changed from (3172) to (1593) pages.
DBCC UPDATEUSAGE: Usage counts updated for table ‘DDD’ (index ‘XXRECID’, partition 1):
USED pages (In-row Data): changed from (1373) to (1369) pages.
RSVD pages (In-row Data): changed from (2765) to (1393) pages.
DBCC UPDATEUSAGE: Usage counts updated for table ‘SYSLOG’ (index ‘SYSLOG’, partition 1):
DATA pages (In-row Data): changed from (4015) to (4016) pages.
USED pages (In-row Data): changed from (4048) to (4017) pages.
RSVD pages (In-row Data): changed from (102408) to (5137) pages.
DBCC UPDATEUSAGE: Usage counts updated for table ‘SYSLOG’ (index ‘SYSDATE’, partition 1):
RSVD pages (In-row Data): changed from (1104) to (1105) pages.
DBCC UPDATEUSAGE: Usage counts updated for table ‘SYSLOG’ (index ‘SYSSITEID’, partition 1):
RSVD pages (In-row Data): changed from (1528) to (1529) pages.
DBCC UPDATEUSAGE: Usage counts updated for table ‘SYSLOG’ (index ‘SYSPID’, partition 1):
RSVD pages (In-row Data): changed from (1664) to (1665) pages.
DBCC UPDATEUSAGE: Usage counts updated for table ‘SYSLOG’ (index ‘SYSRECID’, partition 1):
RSVD pages (In-row Data): changed from (1969) to (994) pages.
DBCC UPDATEUSAGE: Usage counts updated for table ‘EEE’ (index ‘EEE’, partition 1):
Once done, my DBCC reported no issues and now i’ve a good reason to enjoy rest of my day:)
BTW my DR Test is going pretty smooth as of now:) Happy Vibes…..;)Wohoooooooooo
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s