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]; GO /* Send output to console, required by DBINFO(). */ DBCC TRACEON(3604); GO /* Using TABLERESULTS for nicely formatted output. */ DBCC DBINFO(DemoRestoreOrRepair) WITH TABLERESULTS; GO
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.