Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Alert to monitor DBCC CheckDB Expand / Collapse
Author
Message
Posted Thursday, November 4, 2010 11:28 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Yesterday @ 2:19 AM
Points: 66, Visits: 340
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
Post #1016070
Posted Thursday, November 4, 2010 2:09 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 5:28 AM
Points: 12,910, Visits: 32,013

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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1016218
Posted Friday, November 5, 2010 4:23 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Yesterday @ 2:19 AM
Points: 66, Visits: 340
Thank you very much, all I need to do now is corrupt a database to test it out!
Thanks again
Giles
Post #1016437
Posted Friday, November 5, 2010 6:06 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Yesterday @ 2:19 AM
Points: 66, Visits: 340
Just a quick note to say I downloaded some sample corrupt databases from http://sqlskills.com/pastConferences.asp
Post #1016472
Posted Monday, June 27, 2011 11:10 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, August 19, 2014 4:24 AM
Points: 100, Visits: 202
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.



Post #1132322
Posted Monday, June 27, 2011 11:45 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 5:28 AM
Points: 12,910, Visits: 32,013
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1132344
Posted Tuesday, June 28, 2011 5:20 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: 2 days ago @ 11:41 PM
Points: 1,058, Visits: 2,991
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

Post #1132775
Posted Tuesday, June 28, 2011 7:28 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, August 19, 2014 4:24 AM
Points: 100, Visits: 202
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



Post #1132875
Posted Tuesday, June 28, 2011 7:35 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 5:28 AM
Points: 12,910, Visits: 32,013
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1132881
Posted Monday, November 28, 2011 4:02 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 10:37 AM
Points: 5,018, Visits: 10,534
Sorry it took me 6 months...
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

Get your two-cent-answer quickly
spaghettidba.com
@spaghettidba
Post #1212804
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse