How to Check Database Integrity and Identify Corrupt Databases Automatically via Script

It is common that DBAs use SQL Server’s Maintenance plan to check the integrity of databases. If the maintenance plan fails during execution, it is understood that one or many databases have failed the integrity check. The DBA then has to check the SQL log or Maintenance plan’s Execution history to identify the corrupted database. The below script will send info about corrupted databases so that a DBA can directly work on the repair rather than wasting time in identifying which database was corrupted.

Advantages of the script:

Database IntegrityUnlike the maintenance plan, you do not have to dig and search for corrupted objects. This script will log info about corrupted objects of a database in a log table. In addition to that, it will send an email to the recipient list with the following info

  1. Database name: name of the database which was scanned for integrity
  2. Corrupted Objects: Info about corrupted objects from the database because of which the integrity check failed
  3. Solution: default solution recommended for fixing the corrupted objects. Mostly it will be repair allow data loss.

Script can be modified:

Identify Corrupt DatabasesThis script works for a single server. However if you have a Central server used for monitoring other SQL servers, you can modify this script to be executed on all servers. You can then collect all the data into a central table and then analyze it for databases that are often getting corrupted. In many cases server’s disk can be a potential root cause for database corruption. Though, fixing a corrupted SQL Server database should be the key focus, always try to find the root cause for the corruption.

IF NOT OBJECT_ID('tempdb.dbo.#t1') IS NULL
    DROP TABLE #t1;
GO

CREATE TABLE #t1 (
    c0 VARCHAR(200)
    ,c1 INT
    ,c2 INT
    ,c3 INT
    ,c4 VARCHAR(7000)
    ,c5 VARCHAR(7000)
    ,c6 INT
    ,c7 INT
    ,c8 INT
    ,c9 INT
    ,c10 INT
    ,c11 INT
    ,c12 INT
    ,c13 INT
    ,c14 INT
    ,c15 INT
    ,c16 INT
    ,c17 INT
    ,c18 INT
    ,c19 INT
    ,c20 INT
    ,c21 INT
    ,c22 INT
    ,c23 INT
    );

DECLARE @sql VARCHAR(6000)
    ,@db VARCHAR(200)

DECLARE c1 CURSOR
FOR
SELECT NAME
FROM sysdatabases -- where name = 'DemoFatalCorruption1' (use Where clause and filter databases if you want to check integrity of few databases)

OPEN c1

FETCH NEXT
FROM c1
INTO @db

WHILE @@FETCH_STATUS = 0
BEGIN
    SET @sql = 'dbcc checkdb(''' + @db + ''') with no_infomsgs, all_errormsgs, tableresults'

    PRINT @sql

    INSERT INTO #t1 (
        c1
        ,c2
        ,c3
        ,c4
        ,c5
        ,c6
        ,c7
        ,c8
        ,c9
        ,c10
        ,c11
        ,c12
        ,c13
        ,c14
        ,c15
        ,c16
        ,c17
        ,c18
        ,c19
        ,c20
        ,c21
        ,c22
        ,c23
        )
    EXEC (@sql)

    DELETE
    FROM #t1
    WHERE c5 IS NULL

    UPDATE #t1
    SET c0 = @db
    WHERE c0 IS NULL

    FETCH NEXT
    FROM c1
    INTO @db
END

CLOSE c1

DEALLOCATE c1

DECLARE @Body1 VARCHAR(max)
DECLARE @Body2 VARCHAR(max)
DECLARE @TableHead1 VARCHAR(max)
DECLARE @TableTail1 VARCHAR(max)

SET @TableHead1 = '<html><head>' + '<H2>Corrupted objects</H2>' + '</Head>' + '<body><table border=1>' + '<Tr bgcolor=#F6AC5D>' + '<td align=center><b>DatabaseName</b></td>' + '<td align=center><b>Corrupted Objects</b></td>' + '<td align=center><b>Solution</b></td></tr>';
SET @TableTail1 = '</table></body></html>';
SET @Body1 = (
        SELECT c0 AS [TD]
            ,c4 AS [TD]
            ,c5 AS [TD]
        FROM #t1
        FOR XML raw('tr')
            ,Elements
        )
SET @Body2 = @TableHead1 + @Body1 + @TableTail1

EXEC msdb.dbo.sp_send_dbmail @profile_name = '<ENTER SQL SERVER DATABASE MAIL PROFILE NAME HERE'
    ,@body = @Body2
    ,@body_format = 'html'
    ,@recipients = 'RECIPIENT LIST. SEPARATE MULTIPLE EMAIL IDS BY ;'
    ,@subject = 'Database maintenance';

Author Introduction:

Neil Varley is a data recovery expert in DataNumen, Inc., which is the world leader in data recovery technologies, including repair Outlook pst file error and excel recovery software products. For more information visit www.datanumen.com

Comments are closed.