Category: DBCC.

dbccLastKnownGood – The 2016 way.

How to check when was the last known good CheckDB for a given database? As of today on all the versions including SQL 2017(Except 2016 SP2), the easiest way we can get that information is by querying boot page of the database and look for “dbi_dbccLastKnownGood” value. You could do that by using DBCC page or DBCC DBINFO(). For folks who are paranoid about running DBCC PAGE or DBCC DBINFO command in production, If you have a monitoring tool which is displaying this information, I am 100% sure that’s exactly what your tool is running behind the scenes. Nothing to worry about, we are just reading the contents, not modifying anything in the boot page. Anyways, what got changed or I should say what was introduced in SQL 2016 SP2?

Well, we can pull this information from our good old friend DATABASEPROPERTYEX()  🙂

The Microsoft documentation says “Starting SQL 2016 SP2”. Okay, let’s test this on SQL 2017. I have a test database named “packers” on which I ran checkdb around 10:56 PM 05/14/2018 successfully. FYI

Let’s verify by pulling the info from DATABASEPROPERTYEX() on my SQL 2017 RTM Instance.

Oops! It returned NULL. So, basically it’s saying that we provided an invalid Input. Hmm, that’s not good!

Alright, Let’s check what does that report on a SQL Server 2016 SP2 Instance.
From Boot page:

Now, let’s pull the info from Databasepropertyex().


Yeyyy….That works! So….As of today, May 14th 2018, looks like this only works on SQL 2016 SP2 which was released just few weeks ago. I hope Microsoft fix their documentation or get this thing implemented in SQL 2017 in the next CU.

I wonder why it took so many years for Microsoft to implement this simple feature. Anyways we have it now and we don’t need to worry about querying boot page to pull this basic yet very useful/critical information.

Cheers!

Advertisement

Your Database Whereabouts…!

First of all, I would like to wish you all a very Happy and prosperous new year.  This will be my first post of this year 2014 and I would like to show you something interesting- How to get the Hidden facts about your Database. It’s like running a background check on your SQL Server database and accessing it’s PII 😀

Let me ask you this, in general how would you check when your database has been created? What about, where was it actually created? Is it on the same Instance or has this been migrated(or even upgraded from a previous version)? How do you get all these details?

I’ll show you how in this blog post…

I’ve a database called “salesDB” on my SQL Server 2012 Test Instance and I would like to perform a back ground check on the database. Where should I start from? From Properties in SSMS, From Error Log, or by querying sys.databases? Well, unfortunatley all of the above options won’t give me enough information on what am looking for  as you can see below 😦

1

I really can’t trust this info! So…what do we do now?

Well, each and every SQL Server database has something called “Boot Page” where it stores all the metadata related to the respective database and the boot page will be always 9th page of the 1st file in a database. So, reading the Boot Page will expose the info which I’m looking for. But how do we read a Boot page? We’ve two options…

1. DBCC DBINFO()

2. DBCC PAGE(database_name,1,9,3) — File 1, Page 9, dump style 3.

I will use DBCC DBINFO() WITH TABLERESULTS to avoid using traceflag 3604 and 3605. See below Screenshot

2

As you can see

dbi_modDate is “2006-06-04” – This is when it was actually created.

dbi_createVersion is “611” – Which is SQL Server 2005.

So, this has been originally created on SQL server 2005 and has been migrated to SQL Server 2012(dbi_version: 706). Wow…

Also, If you have the original backup file, you can simply do a RESTORE HEADER ONLY for even more info…or you can also query msdb..backupset. See below Screenshot to see what am talking about…

3

Tadaaa…as you can see in the above screenshot, this database has been originally created by Kimberly Tripp on SQLDEV01 Instance. ( Yes, this is a database which I downloaded from sqlskills.com) and I learnt about Boot Page and DBINFO from Paul Randal’s blog posts long ago.

This is how you can perform a “PostMortem” on your SQL Server Database. Hope you learnt something new…

Few Useful(often Ignored) DBCC Commands…

Thought of compiling all the useful DBCC Commands for DBA related to Memory Management within SQL Server. Well,  I’ve seen many DBA’s unaware of the below very useful commands staying under assumption, CHECKDB is the only DBCC Command we’ve in SQL Server.

Okay! Let’s see what do SQL Server offer to DBA’s to do  a diagnosis of SQL Server Memory.

DBCC DROPCLEANBUFFERS : This Command will remove all the Data Pages from the Buffer Cache. Basically, you can issue this if you want to clear your Buffer Cache.

DBCC FREEPROCCACHE: This command will remove all the execution plans from Procedure Cache.

DBCC FREESYSTEMCACHE(‘ALL’) : This command will remove all the Unused entries from all the available caches. It includes Plan cache as well!

DBCC FREESESSIONCACHE : This command will Flush the Distributed Query Connection(Linked Servers) Cache on SQL Instance.

DBCC FLUSHPROCINDB(DB_ID) : This is a pretty useful command which clears Query Plans related to a given Database.
Note: This is an Undocumented Command. 

Hope this is useful! Be very cautious and understand what you are doing before issuing these Commands blindly on your Production SQL Instances to resolve an issue, especially on an Instance which is shared by Multiple applications with many Databases.

CHECKDB in ErrorLogs on STARTUP

Couple of days ago, one of my colleagues observed in the error logs saying, “CHECKDB for database Completed on the database(s) blabla” when she restarted SQL Server Services and she asked me how come SQL Server is running Checkdb within matter of seconds when SQL Services are restarted where as it takes hours and hours when we run Manual CheckDB’s?? (FYI few of our Databases are 450 +GB in size on this Instance).
I really had no answer at that moment?
After refreshing my mind, I recalled the statement of P Randal saying theirs is nothing in SQL Server which automatically runs CHECKDB for you and Crash recovery of SQL Server won’t run DBCC CHECKDB.
IMHO Paul is the best (I mean it) resource for all your questions and doubts in regards with CHECKDB (after all he’s the one who wrote that code). I blindly follow whatever he says without any hesitation anything in SQL Server and I’m a proud reader of his blogJ
Well, So..this made me to research a little bit further on what’s really happening behind the scenes!
Before going any further I want to say a little bit about Boot Page.
FYI Boot page is the one which stores the most critical pieces of information regarding any Database and the boot page is always page 9 of your 1st file in your Database. If your boot page is corrupted, your database is literally gone!  Can you imagine SQL Server running without MASTER Database or MASTER database being corrupted? Nooooooooooo right?? Similarly you can’t imagine any Database with a corrupted Boot page. Hope I had a good analogy 😉
So, why did I mention about Boot page first of all? Coz…that’s the place where you can find the information regarding when was the last clean DBCC ran against your database.
But how to look at that? Using DBCC PAGE.
I’ll check on my Master Database.

Well, It returned nothing! You’ve to enable traceflag 3604 to dump the results to your results pane or 3605 to dump results to your error log.




so it’s saying dbi_dbcclastknowngood = 2011-06-20 20:04:48.617.
If you are not comfortable with DBCC PAGE, you can make use of DBCC DBINFO(‘database’). Please note, even this requires traceflag 3604/3605 to be enabled. If you want to get the results in a tabular format, you can make use of WITH TABLERESULTS.
Now, I’ve restarted my SQL Server and let’s see what’s there in our Error Logs.

If you see it thoroughly it’s basically saying that DBCC was last ran on master database at 2011-06-20 20:04:48.617 but not at the current time when I restarted SQL Server. SQL is pulling that information from Boot Page of the respective database.

So. I learnt a lesson that we’ve to keep a very close eye on the Date and Time in error log besides the content, which we ignore very often for informational messages!
Note: DBCC PAGE, DBINFO and TABLERESULTS are undocumented commands.  If you are not comfortable using them, please don’t use them on your SQL Servers.

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