How long since you ran DBCC CHECKDB?

If you’re not regularly looking for corrupt databases with DBCC CHECKDB, you’re putting your organization’s data at risk. I run DBCC CHECKDB once per day, or as is reasonably possible. Typically, DBCC CHECKDB is setup as part of nightly maintenance.

If you’re not regularly looking for corrupt databases, you’re putting your organization’s data at risk!

Hopefully, you have SQL Server Agent jobs running DBCC CHECKDB, and when those jobs fail or report corruption, you’re all over fixing the problem. But, what if you create a database and forget to setup a scheduled DBCC CHECKDB job? The script below reports when the last checkdb operation completed for each database, with a column showing “at-risk” databases with an “X”; that is databases where the last DBCC CHECKDB occurred more than @MaxDaysWithoutCheckDB days ago. Modify that parameter to suit your needs.

SET NOCOUNT ON;

DECLARE @MaxDaysWithoutCheckDB int = 1;

IF OBJECT_ID(N'tempdb..#Results') IS NOT NULL
BEGIN
    DROP TABLE #Results;
END

CREATE TABLE #Results
(
    DatabaseName sysname NULL
    , IsOnline bit NULL
    , IsReadonly bit NULL
    , ParentObject varchar(100) NULL
    , [Object] varchar(100) NULL
    , [Field] varchar(100) NULL
    , [Value] varchar(100) NULL
);

DECLARE @cmd nvarchar(1000);
DECLARE @dbName sysname;
DECLARE @IsOnline bit;
DECLARE @IsReadonly bit;

DECLARE cur CURSOR LOCAL FORWARD_ONLY STATIC READ_ONLY
FOR
SELECT DBCCCommand = 'DBCC DBINFO(''' + d.name + ''') WITH TABLERESULTS;'
    , DatabaseName = d.name
    , IsOnline = CONVERT(BIT, CASE WHEN d.state_desc = 'ONLINE' THEN 1 ELSE 0 END)
    , IsReadonly = d.is_read_only
FROM sys.databases d
WHERE d.name <> N'tempdb'
    AND d.name <> N'model'
ORDER BY d.name;

OPEN cur;
FETCH NEXT FROM cur INTO @cmd, @dbName, @IsOnline, @IsReadonly;

WHILE @@FETCH_STATUS = 0
BEGIN
    RAISERROR (@dbName, 0, 1) WITH NOWAIT;
    IF @IsOnline = 1
    BEGIN
        INSERT INTO #Results (ParentObject, [Object], [Field], [Value])
        EXEC sp_executesql @cmd;
        UPDATE #Results 
        SET DatabaseName = @dbName
            , IsOnline = @IsOnline
            , IsReadonly = @IsReadonly
        WHERE DatabaseName IS NULL;
    END
    ELSE
    BEGIN
        INSERT INTO #Results (DatabaseName, IsOnline)
        VALUES (@dbName, @IsOnline)
    END

    FETCH NEXT FROM cur INTO @cmd, @dbName, @IsOnline, @IsReadonly;
END

CLOSE cur;
DEALLOCATE cur;

SELECT ServerName = @@SERVERNAME
    , DatabaseName = r.DatabaseName
    , LastKnownGoodDate = CONVERT(DATETIME, r.Value, 120)
    , AtRisk = CASE WHEN DATEDIFF(day, CONVERT(DATETIME, r.Value, 120), GETDATE()) > @MaxDaysWithoutCheckDB THEN 'X' ELSE '' END
    , IsDBOnline = r.IsOnline
    , IsReadonly = @IsReadonly
FROM #Results r
WHERE r.Field = 'dbi_dbccLastKnownGood'
    OR r.Field IS NULL;

I’ve updated the above script to ensure compatibility with case-sensitive collations (thanks Konstantin Taranov for catching that!), and in response to the comment below from Robert, have added an Isreadonly column to the output to show when a database is read-only.