Sql-server – MAIL DBCC CHECKDB RESULTS

database-maildbcc-checkdbsql-server-2012

I want to regularly run DBCC CHECKDB on my production database. My database is real-time and DBCC CHECKDB is very expensive query, that`s why I want to write a maintenance plan to execute the query on nights when the activity is low. I want to set the query results to be sent as mail in order to check it when I am able. My question is, how can I set the query results to be sent to my e-mail when it is executed ?

Best Answer

I'm sure there may be other ways of doing this, but at my shop, I run DBCC CHECKDB via SQLCMD bat files and use the -o parameter to route the output to a flat text file. Then, in the same bat file, I use SQLCMD to invoke 

EXEC msdb.dbo.sp_send_dbmail

and attach the results of the DBCC

This process could be scheduled via either Windows Task Scheduler or you could create a Sql Agent job to run 'Operating System (CMDEXEC) to invoke the SQLCMD.

If you wanted to loop through each online database and run CHECKDB, you could create a utility stored procedure like this with the DBCC options that you want to use. This example skips TEMPDB, but I run a separate automated task for that database alone due to frequent timeouts.

It prints the database name, start time and end time.

Create PROCEDURE [dbo].[DBCC_CheckAllDatabases]
AS
BEGIN
    DECLARE @Message VARCHAR(max);
    DECLARE @databaseList as CURSOR;
    DECLARE @databaseName as NVARCHAR(500);
    DECLARE @tsql AS NVARCHAR(500);

    SET @databaseList = CURSOR  LOCAL FORWARD_ONLY STATIC READ_ONLY 
    FOR
          SELECT QUOTENAME([name])
          FROM sys.databases
          WHERE [state] = 0 and [name]<>'tempdb';
    OPEN @databaseList;
    FETCH NEXT FROM @databaseList into @databaseName;
    WHILE @@FETCH_STATUS = 0
    BEGIN
       SET @Message = 'Starting DBCC for ' + @databaseName + ' -> (' + convert(varchar,getdate(),121) + ')'
       RAISERROR(@Message,0,1) WITH NOWAIT
       SET @tsql = N'DBCC CheckDB(' + @databaseName + ') WITH ALL_ERRORMSGS,NO_INFOMSGS,PHYSICAL_ONLY;';
       RAISERROR(@tsql,0,1) WITH NOWAIT
       EXECUTE (@tsql);
       SET @Message = 'Completed DBCC for ' + @databaseName + ' -> (' + convert(varchar,getdate(),121) + ')'
       RAISERROR(@Message,0,1) WITH NOWAIT

       FETCH NEXT FROM @databaseList into @databaseName;
    END
    CLOSE @databaseList;
    DEALLOCATE @databaseList;
END

You could also utilize Aaron Bertrand's InEachDB process, if you don't like my example for looping through the databases.

Related Question