DBCC DBINFO() – Don’t rely on undocumented commands

Don’t Rely on Undocumented Commands – A Cautionary Tale

Looking at the output for the (undocumented) DBCC DBINFO() command, I noticed a typo in one of the field names. I wondered about submitting it as a bug, but then looked at some of the other SQL Servers available to me, and saw that it appears to have been fixed.

Running this query:

DECLARE @DBCCDBInfo AS TABLE (ParentObject VARCHAR(MAX), Object VARCHAR(MAX), Field VARCHAR(MAX), Value VARCHAR(MAX));

INSERT INTO @DBCCDBInfo
EXEC ('sp_executesql @statement=N''SET NOCOUNT ON; USE Coredata; DBCC DBINFO() WITH tableresults''');

SELECT  SERVERPROPERTY('ProductVersion') AS ProductVersion,
        Field
FROM    @DBCCDBInfo
WHERE   Field LIKE 'dbi_Cont%';

…against an assortment of SQL Server versions produces slightly different output:

ProductVersion	Field
11.0.6260.1	dbi_ContianmentState
11.0.6615.2	dbi_ContianmentState
11.0.7462.6	dbi_ContianmentState
11.0.7469.6	dbi_ContianmentState
12.0.5214.6	dbi_ContainmentState
12.0.5571.0	dbi_ContainmentState
12.0.6024.0	dbi_ContainmentState
13.0.4522.0	dbi_ContainmentState
13.0.5026.0	dbi_ContainmentState
13.0.5081.1	dbi_ContainmentState
13.0.5216.0	dbi_ContainmentState

I don’t have every version available to test, but it does appear that the record indicating Containment State is mis-spelled in SQL Server 2012 as “dbi_ContianmentState”, and then corrected in SQL 2014 and later. It’s a good job I’m not relying on it for anything.

So, yes, don’t rely on undocumented functions – as they may change without notice.

Easier way to check for Containment

Mind you, for what I was actually looking at – the containment state – there’s a much easier way:

SELECT name, containment_desc FROM sys.databases

Containment_desc is either “NONE” or “PARTIAL”. Why only those two? Presumably because they haven’t got as far as “FULL” yet. I vaguely recall conference sessions back when this stuff was being announced indicating that they were looking at incorporating scheduled tasks in the database as well, but this was a few years ago. Maybe they’ll get round to it and make a more contained database.

 

Now, why was I here?

Oh yes.

When was DBCC Last Run?

You can use the same query to check for last known good DBCC check without having to faff about parsing error logs. Search for “Field LIKE ‘%dbcc%'” instead:

SELECT  Field, Value
FROM    @DBCCDBInfo
WHERE   Field LIKE '%DBCC%';

And you get two records:

Field	Value
dbi_dbccFlags	2
dbi_dbccLastKnownGood	2019-07-12 23:36:54.447

Search for dbi_dbccLastKnownGood for the date/time stamp. (The dbccFlags value indicates that DBCC was run WITH DATA_PURITY have been performed.)

But, y’know, undocumented and all, so be aware that this could change…