DBCC CheckDB checks database for corruption. It is normal routine to schedule this to be run once a week or every few weeks. And as we all know there is no such thing as non-corruptible storage system. Corruption will occur on some stage – sooner or later.

When I’ll start as a DBA for a SQL Server which has been in production some time already, I’ll first want to see if everything is ok. After I sign in it is my responsibility. It doesn’t matter whether the corruption in critical database is 1 hour old or 2 months old, it is at DBA’s responsibility.

I also do quite a many SQL Server health checks every month and no matter whether customer has ordered for me to do one thing I do for sure: I’ll look that DBCC CheckDB runs regularly on the server. There is no menu button on management studio or simple DMV for looking the info as everyone very well knows. Infrormation of the most recent successful DBCC CheckDB is found from every database’s file #1 and from page #9. That’s no secret either. You can look for Paul Randall’s blog post “CHECKDB From Every Angle: When did DBCC CHECKDB last run successfully?” for more information.

But if you regularly need to check new servers and perhaps do that over multiple databases you’ll soon find yourself from creating a stored procedure for that. I found myself doing that last summer. I decided to do a small blog post for that because I’ve found myself sending this small script for multiple friends of mine and if the script is available online it is easier for everyone to get their hands into that.

So here it is: sp_DBCCInfo. Takes one argument: DbName varchar(200). If parameter is given it will print only that database if you pass in NULL it will print out every database.

You can download the Stored Procedure here.

— =============================================
— Author:        Vesa Tikkanen
— Create date: 2012-09-12
— Description:    Prints dataset containing datetime when was the DBCC CheckDB ran for DB.
— If dbName
— is NULL all databases are returned.
— =============================================

CREATE PROCEDURE sp_DBCCInfo
    @DbName varchar(200)
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @tmpDbName varchar(200);


    IF OBJECT_ID(‘tempdb..#tempDbs’) IS NOT NULL
    BEGIN
        drop table #tempDbs;
    END

    create table #tempDbs (
        DbName varchar(200),
        LastDBCC varchar(200)
    );

    IF @DbName IS NULL
    BEGIN
        DECLARE database_cursor CURSOR FOR
        SELECT name from sys.databases with (NOLOCK);
    END
    ELSE
    BEGIN
        DECLARE database_cursor CURSOR FOR
        SELECT name from sys.databases with (NOLOCK) where name=@DbName;
    END


    OPEN database_cursor

    FETCH NEXT FROM database_cursor INTO @tmpDbName

    WHILE @@FETCH_STATUS = 0
    BEGIN
        
                            
        IF OBJECT_ID(‘tempdb..#temppi’) IS NOT NULL
        BEGIN
            drop table #temppi;
        END

        

        create table #temppi (
            [ParentObject] varchar(200),
            [Object] varchar(200),
            [Field] varchar(200),
            [VALUE] varchar(200)
        );


        insert into #temppi
            execute(‘DBCC TRACEON (3604); DBCC PAGE (”’ + @tmpDbName +”’, 1, 9, 3) with tableresults’)

        insert into #tempDbs
            select @DbName,VALUE from #temppi where Field=’dbi_dbccLastKnownGood’ group by VALUE;

        FETCH NEXT FROM database_cursor INTO @DbName
    END

    CLOSE database_cursor;
    DEALLOCATE database_cursor;

    select * from #tempDbs;

END
GO