November 4, 2010 at 11:28 am
Hello,
I've configured an Alert to fire when DBCC CheckDB runs for a given Database by monitoring for EventID 8957, at that point it runs a predefined job.
I've tampered with using the keyword attribute but what I really want it to do is only run the job when it finds an error. If I had a damaged DB then I could monitor what got written to the Application Log but I haven't.
So my question is how can I monitor a DBCC CheckDB returning problems? is EventID 8957 only returned when everything is OK?
Declare @AlertName varchar(50);set @AlertName='Respond to CheckDB Failure'
EXEC msdb.dbo.sp_add_alert
@name=@AlertName,
@Database_Name='Example',
@Job_Name='JobName',
@message_ID=8957,--CheckDB message
@event_description_keyword ='DBCC'
Many thanks
Giles
November 4, 2010 at 2:09 pm
Gianluca Sartori here on SSC posted the code below in another thread, and i really like how well it works. this might help you with what you were after...it pretty much only emails errors/issues.
http://www.sqlservercentral.com/Forums/Topic1009121-146-1.aspx
To get a notification, you can set up a job that runs the sp as query for a dbmail.
--Proc from Gianluca Sartori @SSC
--add to a job to send results of sp as mail
--updated table definition based on real world experience from
--Bliar (jbhauser @SSC)
CREATE PROCEDURE [maint].[dba_runCHECKDB]
@dbName sysname = NULL,
@PHYSICAL_ONLY bit = 0,
@allMessages bit = 0
AS
BEGIN
IF OBJECT_ID('tempdb..#DBCC_OUTPUT') IS NOT NULL
DROP TABLE #DBCC_OUTPUT
CREATE TABLE #DBCC_OUTPUT(
Error int NOT NULL,
[Level] int NOT NULL,
State int NOT NULL,
MessageText nvarchar(256) NOT NULL,
RepairLevel varchar(255) NULL,
Status int NOT NULL,
DbId int NOT NULL,
ObjectId int NOT NULL,
IndexId int NOT NULL,
PartitionId bigint NOT NULL,
AllocUnitId bigint NOT NULL,
[File] int NOT NULL,
Page int NOT NULL,
Slot int NOT NULL,
RefFile int NOT NULL,
RefPage int NOT NULL,
RefSlot int NOT NULL,
Allocation int NOT NULL
)
DECLARE c_databases CURSOR LOCAL FAST_FORWARD
FOR
SELECT Name
FROM master.sys.databases
WHERE Name = ISNULL(@dbName, Name)
OPEN c_databases
FETCH NEXT FROM c_databases INTO @dbName
WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE @sql nvarchar(4000)
SET @sql = 'DBCC CHECKDB('+ @dbName +') WITH TABLERESULTS, ALL_ERRORMSGS'
IF @PHYSICAL_ONLY = 1
SET @sql = @sql + ', PHYSICAL_ONLY '
INSERT INTO #DBCC_OUTPUT
EXEC(@sql)
FETCH NEXT FROM c_databases INTO @dbName
END
CLOSE c_databases
DEALLOCATE c_databases
IF NOT EXISTS (
SELECT 1 FROM #DBCC_OUTPUT
)
BEGIN
RAISERROR('No database matches the name specified.',10,1)
END
SET @sql =
'SELECT DB_NAME(DbId) AS DatabaseName, ' +
CASE @allMessages
WHEN 1 THEN '*'
ELSE 'Outcome = CASE WHEN MessageText LIKE ''%0 allocation errors and 0 consistency errors%'' THEN 0 ELSE 1 END, MessageText '
END + '
FROM #DBCC_OUTPUT ' +
CASE @allMessages WHEN 1 THEN '' ELSE 'WHERE Error = 8989' END
EXEC(@sql)
END
Lowell
November 5, 2010 at 4:23 am
Thank you very much, all I need to do now is corrupt a database to test it out!
Thanks again
Giles
November 5, 2010 at 6:06 am
Just a quick note to say I downloaded some sample corrupt databases from http://sqlskills.com/pastConferences.asp
June 27, 2011 at 11:10 am
I am trying to utilize Gianluca's procedure (nice work by the way) against the corrupt databases found in the conference link above. I cannot get around the error "converting data type nvarchar to int". When I run the DBCC CheckDb statement by itself, there is an additional record returned that reads "repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECK DB...".
I believe this additional record is causing the insert statement to fail.
The statement I am running is "DBCC CHECKDB(Corrupt2008Demo) WITH TABLERESULTS, ALL_ERRORMSGS,NO_INFOMSGS". This is executed against a SQL 2008, Standard Edition database with service pack 2 installed.
Does anyone have any suggestions on how to prevent or suppress the informational record from being returned by the DBCC command?
Thanks for any help in advance.
June 27, 2011 at 11:45 am
jbhauser (6/27/2011)
I am trying to utilize Gianluca's procedure (nice work by the way) against the corrupt databases found in the conference link above. I cannot get around the error "converting data type nvarchar to int". When I run the DBCC CheckDb statement by itself, there is an additional record returned that reads "repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECK DB...".I believe this additional record is causing the insert statement to fail.
The statement I am running is "DBCC CHECKDB(Corrupt2008Demo) WITH TABLERESULTS, ALL_ERRORMSGS,NO_INFOMSGS". This is executed against a SQL 2008, Standard Edition database with service pack 2 installed.
Does anyone have any suggestions on how to prevent or suppress the informational record from being returned by the DBCC command?
Thanks for any help in advance.
ok definitely one of the columns in that temp table we created doesn't match the DBCC results, so you'll have to help out: when you get the error you identified when running DBCC all by itself,""repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECK DB...".", look at the row of data...i suspect the column "RepairLevel" is some text instead of NULL? change that temp table to have a varchar definition for the offending varchar column and let us know if that was the issue:
RepairLevel int NULL,
RepairLevel varchar(255) NULL,
Lowell
June 28, 2011 at 5:20 am
Hi,
This is very nice script for quickly get and clear output consistancy checking database for all database.
Can anyone help me, I want to know how ouput file attached or placed to email body?
I configured like at SQLJobs and output stroing in "C" drive (file name consistancy_check.txt)
Thanks
ananda
June 28, 2011 at 7:28 am
Changing the RepairLevel to varchar led me to other errors (thanks for pointing me in the right direction Lowell). I changed the following table columns and am now able to execute the procedure without errors:
MessageText nvarchar(300) NOT NULL,
RepairLevel varchar(255) NULL,
...
...
PartitionId bigint NOT NULL,
AllocUnitId bigint NOT NULL,
Thanks for your help,
Blair
June 28, 2011 at 7:35 am
Bliar thanks for posting what you found back; I updated my post, and PM'd Gianluca to make him aware of what you found;
I'm sure that this will help someone else out in the future.
Lowell
November 28, 2011 at 4:02 pm
Sorry it took me 6 months... :blush:
I posted a corrected version of this code on my blog:
http://spaghettidba.com/2011/11/28/email-alert-dbcc-checkdb/
I hope you will find it useful.
-- Gianluca Sartori
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply