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:
About the author
This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips