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:
Unlike 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
- Database name: name of the database which was scanned for integrity
- Corrupted Objects: Info about corrupted objects from the database because of which the integrity check failed
- Solution: default solution recommended for fixing the corrupted objects. Mostly it will be repair allow data loss.
Script can be modified:
This 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
Leave a Reply