Corruptions, DBCC., SQL Server 2016

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!

Advertisements
Database Options, DBCC.

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…