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

Who are you? Why are you fit to be a DBA?

Why to Become a DBA?

Not sure where to Start!All i Can say is you can be one of the Most(I mean it) Important resources for your Organization.
Imazine preparing Hyderabadi Chicken Biriyani without Basmati Rice(google it if you are not Sure what i’m talking about)…Well Sounds funny huh?
Let’s say Ur Organization has fabulous Web Applications with outstanding interfaces for End Users which ofcourse attracts customers…But Guys…who in this world can access a web page without a healty Database which is sitting somewhere hiding in the Data Center….Who is responsible for that Database? It’s You, a DBA( Feel Proud about what you are doing to your company). You are the Data Gaurdian,a Custodian You are the one who assures that the most critical data is always available for what ever purpose your company needs for!

Can a company Survive without your Application Server for a while? Well it really depends on Tier and who’s interacting with that application. Can a company Survie without Data? Lolz…I would say Nooo independent of what really ur application does!…

Are you a DBA?
You should’ve whole patience in this world with you all the time…and Remember Guys If you are really looking into becoming a DBA, don’t just go for it if you are not prepared for taking responsibility. There are lot of Jobs out there where you can earn money with less pain in your Butt! You need a real commitment and should be smart enough to take wise decisions in critical situations on your own. IMHO, Large companies will not keep there life/death data to Just anyone. personally, i feel Willingness to explore new technologies all the time, new enhancements you’ve for your RDBMS, Share your Knowledge to at least your team Members is bare minimum requirement.
DBA job is not Just running Scripts against your databases as most of people out there are thinking!
Trust me no other DBA in your own team likes you if you are really into Just running Scripts and escaping from work(real DBA work).

Soo…Who are your Friends at your workplace?

A DBA who just has Technical Skills can’t survive in any organization without making friends with his communication Skills.
Beleive me, Never mess with Server Guys and SAN Admins…They are our Best friends sitting somewhere near your Cube.
We DBA’s are pondered with responsibilities and accountable for answering to all !@#$!@!@@# Questions from application team(s)/Developers when a Database Server is not available. Trust me guys, Server Guys are never contacted directly if something happens to your Database Server receiving a PAGE and unfortunatley you are accountable for Doing a Root Cause Analysis in many Cases.

Ideal World which seldom exists almost in any big organization…..:(

Imazine you are the one who builds Windows Cluster and You comeup with Storage Recommendations, you are the one who’s is installing and Configuring SQL Server, you are the one who is designing Security model for your SQL DB Server. Wow….sounds Amazingly Outstanding right?? Unfortunately that only happens in dreams 😦

Okay…Let’s come to reality:
Windows/OSE/Network team will configure cluster.
Your Infrastructure/Architecture team Installing SQL Server cluster and deciding all the RAID Levels and what not.
Conceptually your SQL Server is ready to serve your application Databases at this point since it passed Operational Readiness.

Questions to ask yourself:
were your SQL Servers really tested with required Stress?
There’s a fabulous Tool called SQLIO(Free tool from MSFT SQL Server team, Infact they made it available with your default installation of SQL Server starting SQL 2008)…which you can simulate various work loads.
Were your Drives configured with Proper partition Allignments? Trust me convincing management to these tests to be performed atleast for your Brand new servers really really helps you.(I mean it)
Who else are Sysadmins on your SQL Server?
Why the heck in this World really needs your OU admins/Domain Admins as SysAdmins on your SQL Server? Are they giving us Domain Admin rights if you ask them..No they’ll never. Do the same! Revoke them if you see any. That’s the rule called “Seperation of Duties”. Trust me any SA sitting on your SQL Server can make you loose your Job and nobody in this world would agree that he/she dropped a Database or dropped the Service account or tried to delete your Master Database.

Well It’s Friday….for me:) Will start blogging some real technical stuff real soon….Wish me luck with this!

A newbie for Tech Blogging

It was always hard to me finding sometime blogging some really Cool Stuff in regards with the technologies i worked in past and currently I’m working on….Thought of sharing my experiences and knowledge with the peers sitting around me and learning new things from their vast Experience…Phewww..enough intro for a Tech Blog( I mean it)