How to verify DBCC CHECKDB

Question

I am logging the DBCC CHECKDB job output to a table.

When I look at the job log using the below command, I want to verify that all is well.

use msdb
EXEC dbo.sp_help_jobsteplog
@job_name = N'DatabaseIntegrityCheck - USER_DATABASES' ;

Would you say that we are good if we see "Outcome: Succeeded" in the log for the job or is there something else we should also review?

We are using Ola's job output but I am also logging it to a table and am just wondering about the output. If we see "outcome succeeded' in the job, is this enough to know the CHECKDB found nothing wrong?

Marcello Miorelli · Answer 1 · 2025-02-13T14:43:08.473

to run your dbcc checkdb, check if you have got the available resources.

to then check the what databases have gone through it in a specific server, you can use this script below, from this answer.

/* DBCC CHECKDB LastKnownGood - using a cursor through all databases in the local instance */
IF OBJECT_ID(N'tempdb..#Results') IS NOT NULL
BEGIN
    DROP TABLE #Results;
END
CREATE TABLE #Results
(
    DatabaseName SYSNAME NULL
    , IsOnline BIT NULL
    , ParentObject varchar(100) NULL
    , [Object] varchar(100) NULL
    , [Field] varchar(100) NULL
    , [Value] varchar(100) NULL
);
DECLARE @cmd NVARCHAR(MAX);
DECLARE @dbName SYSNAME;
DECLARE @IsOnline BIT;
DECLARE cur CURSOR LOCAL FORWARD_ONLY STATIC
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
      )
FROM sys.databases d
ORDER BY d.name;
OPEN cur;
FETCH NEXT FROM cur INTO @cmd, @dbName, @IsOnline;
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
        WHERE DatabaseName IS NULL;
    END
    ELSE
    BEGIN
        INSERT INTO #Results (DatabaseName, IsOnline)
        VALUES (@dbName, @IsOnline)
    END
FETCH NEXT FROM cur INTO @cmd, @dbName, @IsOnline;

END
CLOSE cur;
DEALLOCATE cur;
SELECT r.DatabaseName
    , LastKnownGoodDate = CONVERT(DATETIME, r.value, 120)
    , r.IsOnline
FROM #Results r
WHERE r.field = 'dbi_dbccLastKnownGood'
    OR r.field IS NULL;

But if you want a detailed result from your dbcc checkdb then you could head to this link.

from there you can see how you can save the results to a table. I will post part of the code just for you to have an idea:

I think this particular part is the key:

set @sql = 'DBCC CHECKDB ' + '(' + '[' + @database_name + ']' + ')' + ' WITH ALL_ERRORMSGS, DATA_PURITY, tableresults'

But here there is how it is wrapped in code (partial view only)


DECLARE check_databases CURSOR FOR
select 
name, 
database_id
from sys.databases
where database_id  NOT IN (3) -- ignore the model database
and state in (0,4) -- online or suspect mode databases only
and is_read_only = 0
and source_database_id IS NULL -- only real database, so no database snapshots!
order by name;
-- Open the cursor.
OPEN check_databases;
-- Loop through the update_stats cursor.
FETCH NEXT
   FROM check_databases
   INTO @database_name, @database_id;
WHILE @@FETCH_STATUS <> -1 -- Stop when the FETCH statement failed or the row is beyond the result set
BEGIN
IF @@FETCH_STATUS = 0 -- to ignore -2 status "The row fetched is missing"
BEGIN
BEGIN TRY
-- DEBUG
--declare @sql varchar(500)
--declare @database_name sysname
--declare @ver varchar(15)
--set @ver = 'SQL 2014'
--set @database_name = 'DBAdmin'
set @sql = 'DBCC CHECKDB ' + '(' + '[' + @database_name + ']' + ')' + ' WITH ALL_ERRORMSGS, DATA_PURITY, tableresults'
--print @sql;
--exec (@sql);
IF LTRIM(RTRIM(@ver)) not in ('SQL 2012','SQL 2014') -- before version 2012 and 2014
BEGIN
INSERT INTO [your admin database here].[your admin schema here].dba.dbcc_history ([Error], [Level], [State], MessageText, RepairLevel, [Status], 
[DbId], Id, IndId, PartitionId, AllocUnitId, [File], Page, Slot, RefFile, RefPage, 
RefSlot,Allocation) exec(@sql)
END;
ELSE
BEGIN -- for versions 2012 and 2014 only
INSERT INTO [your admin database here].[your admin schema here].dbcc_history_2012
([Error],[Level],[State],[MessageText],[RepairLevel],[Status],[DbId],[DbFragId],[ObjectId],[IndexId],[PartitionId],
[AllocUnitId],[RidDbId],[RidPruId],[File],[Page],[Slot],[RefDBId],[RefPruId],[RefFile],[RefPage],[RefSlot],
[Allocation]) exec(@sql)
END;
END TRY
BEGIN CATCH
SELECT @ERR_MESSAGE = ERROR_MESSAGE(), @ERR_NUM = ERROR_NUMBER();
SET @MESSAGE_BODY='Failure running the DBCC CHECKDB LOGGED job for ' + @database_name + ' Error Message = ' + @ERR_MESSAGE
SET @MESSAGE_SUBJECT='Failure running the DBCC CHECKDB LOGGED job for ' + @database_name + '  on ' + @@SERVERNAME
EXEC msdb.dbo.sp_notify_operator 
@profile_name = @MailProfileName, 
@name=N'Haden Kingsland',
@subject = @MESSAGE_SUBJECT,
@body= @MESSAGE_BODY;
print @MESSAGE_BODY
print @MESSAGE_SUBJECT
END CATCH
--FETCH NEXT FROM check_databases INTO @database_name, @database_id;
END -- end of @@fetchstatus if
FETCH NEXT FROM check_databases INTO @database_name, @database_id;
END
-- Close and deallocate the cursor.
CLOSE check_databases;
DEALLOCATE check_databases;
END

this link is also very informative:

Capture and Store SQL Server Database Integrity History using DBCC CHECKDB

How to verify DBCC CHECKDB

1 Answers1