How long since you ran DBCC CHECKDB?

2019-01-23 · by Hannah Vernon · in maintenance, tools

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;

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.

How long since you ran DBCC CHECKDB?

Related

4 Responses

Leave a ReplyCancel reply

Search

Categories

Pages

Meta