Generate HTML Email for SQL Server Database Consistency Check Errors

By: Nisarg Upadhyay | Comments (3) | Related: > Database Console Commands DBCCs

Problem

How can I email SQL Server database corruption errors when generated by SQL Server DBCC executions?

Solution

As a SQL Server Database Administrator, we must make sure that all databases do not have any corruption. During maintenance windows, we should setup database consistency checks to verify the integrity of each database.

After a database consistency check completes, we can save the output to a table to maintain the history of the consistency check errors. Here is the excellent article which briefly explains the process to store the history of consistency checks.

For data centers that have numerous database servers, it becomes a tedious task to retrieve “database consistency check logs” from each instance. To minimize that effort, I have written a T- SQL script that performs the database consistency check, finds any errors and sends an email with the error messages. Instead of attaching error messages in a text file, the error messages embed within the email body.

The script performs following tasks:

Creates a temp table to store consistency check errors
Generates a dynamic SQL Query which creates a “DBCC CHECKDB” command that performs consistency checks across all the databases. Once the database consistency check completes and it finds consistency errors, it saves the data in the temp table.
Reads the errors from temp table and creates an HTML file, attach it in the email body and send it to desired emails.

SQL Server DBCC CHECKDB Data Collection

When we execute DBCC CHECKDB with table-result, it populates the results with the following column structure. See the following image:

The script performs the consistency checks with table results. To save the results generated by the “DBCC CHECKDB,” we will create a temp table as follows:

   CREATE TABLE #dbcc_output
      (
       [error]       [INT] NULL,
       [level]       [INT] NULL,
       [state]       [INT] NULL,
       [messagetext] [NVARCHAR](max) NULL,
       [repairlevel] [NVARCHAR](max) NULL,
       [status]      [INT] NULL,
       [dbid]        [INT] NULL,
       [dbfragid]    [INT] NULL,
       [id]          [BIGINT] NULL,
       [indid]       [BIGINT] NULL,
       [partitionid] [BIGINT] NULL,
       [allocunitid] [BIGINT] NULL,
       [file]        [INT] NULL,
       [page]        [INT] NULL,
       [slot]        [INT] NULL,
       [reffile]     [INT] NULL,
       [refpage]     [INT] NULL,
       [refslot]     [INT] NULL,
       [allocation]  [INT] NULL,
       [riddbid]     [INT] NULL,
       [ridpruid]    [INT] NULL,
       [refdbid]     [INT] NULL,
       [refpruid]    [INT] NULL
       )

Generate a dynamic query to perform consistency checks

Once the table is created, we create a dynamic SQL query that iterates through all the databases, performs a consistency check and store the errors in the temp table.

   CREATE TABLE #Databases (Name varchar(250))

   INSERT INTO #Databases 
   SELECT NAME
   FROM   sys.databases db
   WHERE  database_id > 9
     AND db.state_desc = 'ONLINE'
     AND source_database_id IS NULL
     AND is_read_only = 0
   
   SET @DBCount=(select count (1) from #Databases)
   
   WHILE (@DBCount>@I)
   BEGIN
      SET @DBName = (SELECT TOP 1 name FROM #Databases)
      SET @sql = 'DBCC CHECKDB(' + @dbName  + ') WITH TABLERESULTS, ALL_ERRORMSGS,NO_INFOMSGS'
      INSERT INTO #dbcc_output EXEC (@sql)
      DELETE FROM #Databases WHERE name=@DBName
      SET @I=@i+1
   END

Format Output with HTML Tags

Once the consistency check completes, the script stores the output in the #dbcc_output table. Instead of displaying the entire output of #DBCC_Output table, the code selects dbid, message and repairLevel columns. To display the columns in tabular format, the code uses HTML tags. The code adds the required HTML tags and stores the output in a local variable @EmailBody. The text within <TH></TH> tag is the header of the table. You can give the desired names to these header tags as needed.

Once the HTML string is formatted, the code uses the “sp_send_dbmail” procedure to send the query output via email.

   DECLARE @subject NVARCHAR(max)
   DECLARE @tableHTML NVARCHAR(max)
   DECLARE @ErrorCount_120 INT

   SET @subject = 'Database Consistancy Check report for Server : ' + @@servername

   SELECT @ErrorCount_120 = Count(*) FROM #dbcc_output

   IF ( @ErrorCount_120 > 0 )
      BEGIN
         SET @tableHTML =
            '     <html>     <Body>     <style type="text/css">      table {font-size:9.0pt;font-family:verdana;text-align:left;}      tr {text-align:left;}      
            h3 {            display: block;        font-size: 15.0pt;        font-weight: bold;        font-family: verdana;        text-align:left;       }    
            </style>     <H3>Summery of Database Consistancy Check on Server '
            + @@servername + '</H3>' + N'<table border="1">'
            + N'<tr>     <th>MessageText</th>     <th>Corrupt Database</th>     <th>RepairLevel</th>     </tr>'
            + Cast((SELECT Isnull(messagetext, '') AS 'TD', '',
            Isnull(
            Db_name(dbid), '') AS 'TD', '', Isnull(repairlevel,
            '')
            AS
            'TD'
            , '' FROM #dbcc_output FOR xml path
            (
            'tr'
            ),
            root) AS NVARCHAR(max))
            + N'</table>      </html>     </Body>'
      END
   ELSE
      BEGIN
         SET @tableHTML = '     <html>     <Body>     <style type="text/css">      table {font-size:9.0pt;font-family:verdana;text-align:left;}     
         tr {text-align:left;}      h3 {            display: block;        font-size: 15.0pt;        font-weight: bold;        font-family: verdana;        text-align:left;       }    
          </style>     <H3>No Consistency Errors Found on Server '
         + @@servername + '</H3>'
      END

   EXEC msdb..sp_send_dbmail 
       @profile_name = 'SQL_AUTO_MAILER'    
      ,@recipients = '[email protected]'    
      ,@subject = @subject    
      ,@importance = 'High'    
      ,@body = @tableHTML    
      ,@body_format = 'HTML';

SQL Server DBCC CHECKDB Email Automation

Here is the compete code. This creates a stored procedure and it can be run as is or you can create a scheduled SQL Server Agent Job to run on a set schedule.

USE msdb
GO

CREATE PROCEDURE Dbasp_checkdb
AS
BEGIN
   DECLARE @DBName varchar(250)
   DECLARE @DBCount int
   DECLARE @I int =0
   DECLARE @sql NVARCHAR(4000)
   
   IF Object_id('tempdb..#DBCC_OUTPUT') IS NOT NULL
        DROP TABLE #dbcc_output

   CREATE TABLE #dbcc_output
      (
       [error]       [INT] NULL,
       [level]       [INT] NULL,
       [state]       [INT] NULL,
       [messagetext] [NVARCHAR](max) NULL,
       [repairlevel] [NVARCHAR](max) NULL,
       [status]      [INT] NULL,
       [dbid]        [INT] NULL,
       [dbfragid]    [INT] NULL,
       [id]          [BIGINT] NULL,
       [indid]       [BIGINT] NULL,
       [partitionid] [BIGINT] NULL,
       [allocunitid] [BIGINT] NULL,
       [file]        [INT] NULL,
       [page]        [INT] NULL,
       [slot]        [INT] NULL,
       [reffile]     [INT] NULL,
       [refpage]     [INT] NULL,
       [refslot]     [INT] NULL,
       [allocation]  [INT] NULL,
       [riddbid]     [INT] NULL,
       [ridpruid]    [INT] NULL,
       [refdbid]     [INT] NULL,
       [refpruid]    [INT] NULL
       )

   CREATE TABLE #Databases (Name varchar(250))

   INSERT INTO #Databases 
   SELECT NAME
   FROM   sys.databases db
   WHERE  database_id > 9
     AND db.state_desc = 'ONLINE'
     AND source_database_id IS NULL
     AND is_read_only = 0
   
   SET @DBCount=(select count (1) from #Databases)
   
   WHILE (@DBCount>@I)
   BEGIN
      SET @DBName = (SELECT TOP 1 name FROM #Databases)
      SET @sql = 'DBCC CHECKDB(' + @dbName  + ') WITH TABLERESULTS, ALL_ERRORMSGS,NO_INFOMSGS'
      INSERT INTO #dbcc_output EXEC (@sql)
      DELETE FROM #Databases WHERE name=@DBName
      SET @I=@i+1
   END    

   DECLARE @subject NVARCHAR(max)
   DECLARE @tableHTML NVARCHAR(max)
   DECLARE @ErrorCount_120 INT

   SET @subject = 'Database Consistancy Check report for Server : ' + @@servername

   SELECT @ErrorCount_120 = Count(*) FROM #dbcc_output

   IF ( @ErrorCount_120 > 0 )
      BEGIN
         SET @tableHTML =
            '     <html>     <Body>     <style type="text/css">      table {font-size:9.0pt;font-family:verdana;text-align:left;}      tr {text-align:left;}      
            h3 {            display: block;        font-size: 15.0pt;        font-weight: bold;        font-family: verdana;        text-align:left;       }    
            </style>     <H3>Summery of Database Consistancy Check on Server '
            + @@servername + '</H3>' + N'<table border="1">'
            + N'<tr>     <th>MessageText</th>     <th>Corrupt Database</th>     <th>RepairLevel</th>     </tr>'
            + Cast((SELECT Isnull(messagetext, '') AS 'TD', '',
            Isnull(
            Db_name(dbid), '') AS 'TD', '', Isnull(repairlevel,
            '')
            AS
            'TD'
            , '' FROM #dbcc_output FOR xml path
            (
            'tr'
            ),
            root) AS NVARCHAR(max))
            + N'</table>      </html>     </Body>'
      END
   ELSE
      BEGIN
         SET @tableHTML = '     <html>     <Body>     <style type="text/css">      table {font-size:9.0pt;font-family:verdana;text-align:left;}     
         tr {text-align:left;}      h3 {            display: block;        font-size: 15.0pt;        font-weight: bold;        font-family: verdana;        text-align:left;       }    
          </style>     <H3>No Consistency Errors Found on Server '
         + @@servername + '</H3>'
      END

   EXEC msdb..sp_send_dbmail 
       @profile_name = 'SQL_AUTO_MAILER'    
      ,@recipients = '[email protected]'    
      ,@subject = @subject    
      ,@importance = 'High'    
      ,@body = @tableHTML    
      ,@body_format = 'HTML'; 
END

Once the procedure is created, schedule a SQL Server Agent Job to execute it on a desired schedule. Open SSMS and expand SQL Server Agent, right click Jobs and select New Job.

In the Job step, enter the following code in the command text box.

exec Dbasp_checkdb

To test this code, I restored a few corrupted databases on my computer. You can download those sample databases from here.

Restore the backup of corrupted database and execute the SQL Server Agent Job. Once the job completes, you’ll receive an email similar to below:

Summary

In this article, I explained how to:

Create T-SQL script to perform DBCC CHECKDB on all SQL Server databases and send an email with consistency errors.
Create stored procedure and SQL Server Agent Job to execute on your desired schedule.

Next Steps

Check out these other resources:
- SQL Server database consistency check tips.
- Tip on how to generate HTML formatted emails.

About the author

Nisarg Upadhyay is a SQL Server Database Administrator and Microsoft certified professional with more than 5 years of experience.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Generate HTML Formatted Email of SQL Server Database Consistency Check Errors