Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Ctrl-alt-geek

Matt Bowler is a DBA at Trade Me, and teaches database design and administration courses at the local institute of technology. A recent but avid discoverer of SQL Server, Matt is a regular contributor to forums at MSDN, Experts Exchange and SSC and he blogs at mattsql.wordpress.com.

Quick tip: Querying database metadata with DBCC DBINFO

Page 9 in file 1 (of the primary filegroup) in a SQL Server database is the header page and contains a wealth of metadata about the database. We can have a look at this with DBCC PAGE or DBCC DBINFO.

In order to redirect the output of the DBCC command to the results we first need to enable trace flag 3604.

DBCC TRACEON(3604)

We can then look at the header page with:

DBCC PAGE(<database name>,1,9,3)

Where 1 is the file number, 9 the page number and 3 the dump style (possible values for this parameter are 1,2 or 3).

Another option is to use this:

DBCC DBINFO(<database name>)

You can run this command without a database name parameter and it will return the header page for the current database.

You can also redirect the output in table form with the tableresults option:

DBCC DBINFO(<database name>) with tableresults

(You don’t need to run the trace flag when using tableresults).

This then naturally leads to using a table variable, loading it with the DBCC output and then having the ability to query the set.

When was the last log backup?

declare @dbinfo table
(
ParentObject varchar(255),
[Object] varchar(255),
[Field] varchar(255),
[Value] varchar(255))
insert into @dbinfo
execute('dbcc dbinfo(''<database name>'') with tableresults')
select Field, Value from @dbinfo
where Field = 'dbi_LastLogBackupTime'

When was the last clean DBCC check?

declare @dbinfo table
(
ParentObject varchar(255),
[Object] varchar(255),
[Field] varchar(255),
[Value] varchar(255))
insert into @dbinfo
execute('dbcc dbinfo(''<database name>'') with tableresults')
select Field, Value from @dbinfo
where Field = 'dbi_dbccLastKnownGood'

Caveat: DBCC PAGE and DBCC DBINFO are commonly used but they are undocumented and unsupported commands.


Comments

Leave a comment on the original post [mattsql.wordpress.com, opens in a new window]

Loading comments...