Often, I like to poke around inside databases just for fun. Just to look at the structures. It’s kind of like wandering around unexplored territory, and recognizing familiar architectures – appealing to my sense of curiosity. My first tool for the task is usually the undocumented DBCC DBINFO(). While I normally don’t encourage the use of undocumented commands, this one is safe, and doesn’t appear to be going anywhere.

Let’s look at the results for a sample database, and discuss the output. Run the following code on the database of your choice. The DB results shown here are from a demonstration database from my corruption presentation:

USE [master];
/* Send output to console, required by DBINFO(). */
/* Using TABLERESULTS for nicely formatted output. */
DBCC DBINFO(DemoRestoreOrRepair)
DBINFO Results
Click to enlarge.

We get a lot of information out of this. Much of it can also be obtained by querying sys.databases as well, but some cannot. The most interesting bits to me are:

  • dbi_Version and dbi_CreateVersion. – Tells you what version of SQL Server the database was created in, and is currently. A mismatch here indicates the DB has been upgraded.
  • dbi_DBCCFlags – A value of 2 tells you that data purity checks are being run by default when CHECKDB runs on this database. 0 means they are not, and you’ll have to specify that as an option for the CHECKDB command next time you run it. (If you *want* data purity checks, that is.)
  • dbi_DBCCLastKnownGood – The last time DBCC CHECKDB was run successfully on this database.
  • dbi_LastLogBackupTime – Just what it says on the label, the last time the log was backed up.

There are also a lot of entries about the various log sequence numbers that the database was on at specific points in time. Such as:

  • dbi_dbbackupLSN for the last backup.
  • dbi_DifferentialBaseLSN for the last full backup, which would be required to start the restore sequence.
  • dbi_checkptLSN for the last checkpoint.
  • dbi_DirtyPageLSN for the last time a transaction was committed.

Interesting stuff, I think.  What about you?

Thanks for reading.



Please Comment

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 )

Google+ photo

You are commenting using your Google+ 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 )

Connecting to %s