July 28, 2011 at 3:07 pm
I'm looking for a script (Powershell?) which will scan a directory of the batch job log files produced by running DBCC CHECKDB as a batch SQL Server Agent job against a series of databases on a server. Each run produces text log file that, hopefully, includes near the bottom the line:
CHECKDB found 0 allocation errors and 0 consistency errors in database 'test'. [SQLSTATE 01000]
The script should scan (grep) all of the log files and, ideally, email a message when either error count is not zero.
Has anyone seen a script like this?
Thank you for any help.
Alan
(apologies for the cross post with SS08 Admin)
July 28, 2011 at 3:38 pm
Ping me back tomorrow morning if I forget. I have something even better than that.
July 29, 2011 at 10:00 am
This creates your log table
USE [msdb]
GO
/****** Object: Table [dbo].[DBA_CheckDB] Script Date: 07/29/2011 11:50:01 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[DBA_CheckDB](
[Error] [int] NULL,
[Level] [int] NULL,
[State] [int] NULL,
[MessageText] [nvarchar](2048) NULL,
[RepairLevel] [nvarchar](22) NULL,
[Status] [int] NULL,
[DbId] [smallint] NULL,
[ObjectId] [int] NULL,
[IndexId] [int] NULL,
[PartitionId] [bigint] NULL,
[AllocUnitId] [bigint] NULL,
[File] [smallint] NULL,
[Page] [int] NULL,
[Slot] [int] NULL,
[RefFile] [smallint] NULL,
[RefPage] [int] NULL,
[RefSlot] [int] NULL,
[Allocation] [smallint] NULL,
[DbName] [sysname] NULL
) ON [PRIMARY]
GO
CREATE CLUSTERED INDEX [IX_DBA_CheckDB] ON [dbo].[DBA_CheckDB]
(
[DbName] ASC,
[ObjectId] ASC,
[IndexId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
This is what I put in my job :
INSERT INTO msdb.dbo.DBA_CheckDB
EXEC ('DBCC CHECKDB(''PROD-FORDIA'') WITH TABLERESULTS, NO_INFOMSGS, ALL_ERRORMSGS')
UPDATE msdb.dbo.DBA_CheckDB SET DbName = 'PROD-FORDIA' WHERE DbName IS NULL
IF @@ROWCOUNT <> 0 --corruption in DB, disable all backup jobs, warn admins
BEGIN
EXEC msdb.dbo.sp_update_job @job_name=N'Backup PROD-FORDIA NEW', @enabled=0
EXEC msdb.dbo.sp_update_job @job_name=N'Delete old backups', @enabled=0
EXEC msdb.dbo.sp_update_job @job_name=N'Backup PROD-FORDIA LOG NEW', @enabled=0
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'e-mail profile',
@recipients = 'e-mail list',
@query = 'SET NOCOUNT ON; SELECT * FROM msdb.dbo.DBA_CheckDB' ,
@subject = 'ERREUR FATALE, CORRUPTION DE LA BD PROD-FORDIA',
@attach_query_result_as_file = 1,
@body = 'Voici la query qui réaffiche les résultats de CHECKDB : SELECT * FROM msdb.dbo.DBA_CheckDB',
@importance = 'HIGH',
@query_result_separator = '|',
@file_attachments = '\\Fordiavcenter4\Informatique\Navision backup\Help, my database is corrupt_ Now what - SQLServerCentral.mht' -- nvarchar(max);
END
The article file can be found here (requires to be logged in on the site, so I preffer to send the file over) : http://qa.sqlservercentral.com/articles/Corruption/65804/
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply