Generate HTML Formatted Email of SQL Server Database Consistency Check Errors

By:   |   Updated: 2018-10-02   |   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:

  1. Creates a temp table to store consistency check errors
  2. 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.
  3. 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:

dbcc checkdb output

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.

create sql agent job

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

exec Dbasp_checkdb
sql agent job step

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:

consistency check error email

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


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Nisarg Upadhyay 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


Article Last Updated: 2018-10-02

Comments For This Article




Wednesday, October 24, 2018 - 5:58:04 AM - Wilfred van Dijk Back To Top (78042)

 Hi,

In your final code, you select databases where database_id > 9. I assume this is a leftover from a test? 

 

Wednesday, October 3, 2018 - 11:00:24 AM - Jason Back To Top (77823)

I'm just curious why you included the "database_id > 9" in the WHERE clause for selecting databases?

Tuesday, October 2, 2018 - 12:30:32 PM - Kumar Visisth Sinha Back To Top (77784)

Very helpful and learnt new idea. 














get free sql tips
agree to terms