DBCC CheckDB errors

Question

DBCC CheckDB errors

mtz676

SSCrazy Eights

Points: 9204
More actions
February 27, 2024 at 12:25 pm

#4385676

How can I identify automatically(like email alert if error shows up) through code if DBCC check db reported any errors.

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Grant Fritchey SSC Guru Points: 398815 More actions · Answer 1

I assume you're using something to automate the running of DBCC. When that something gets an error, have it send an email. That's it. So, how are you running DBCC?

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

cyrusbaratt Old Hand Points: 356 More actions · Answer 2

You could do something like this:

NSERT INTO @Tmp --> Internal SQL table

EXECUTE('DBCC checkdb ('DBNAME')');

SELECT * FROM @Tmp where field like '%ERROR%'

DBASupport

mtz676 SSCrazy Eights Points: 9204 More actions · Answer 3

As part of a SQL jobs which dbcc's all user dbs and if any the dbs report an error then I need an email stating the same...that this db has dbcc errors.

mtz676 SSCrazy Eights Points: 9204 More actions · Answer 4

mtz676

SSCrazy Eights

Points: 9204

February 28, 2024 at 4:43 am

#4386081

Will try that.Thanks

mtz676 SSCrazy Eights Points: 9204 More actions · Answer 5

Hello Grant/All,

I have a some databases I would like to dbcc checkdb and sent an email alert if any errors occurs in any of the databases.

I am running DBCC as part of a SQL Job. The DBCC runs through if no errors are found on any of the databases. But if there are errors , how do I tackle it.

Lets say I have 10 databases if 3 dbs have errors I need an email stating the database names and just some text stating that the DBs have integrity issues. Nothing in detail. How do I achieve this.

DB1 - Integrity Issue

DB2 - Integity Issue

DB3 - Integrity Issue

Jeff Moden SSC Guru Points: 1004166 More actions · Answer 6

Please see the following article for how to capture the results for all databases. What most people don't know is that most of the DBCC commands will allow for the WITH TABLERESULTS option (even though the MS documentation doesn't say so in most cases), which returns the output as a table without having to use trace flags, etc, etc. That also means they can be captured into a table (personally, I'd probably not use a cursor like this does but there's no harm here).

https://www.mssqltips.com/sqlservertip/2325/capture-and-store-sql-server-database-integrity-history-using-dbcc-checkdb/

Once that is done, they can be queried for errors and the results of that final query can be sent by email in a couple of different ways. Read up on sp_send_dbmail at this next link.

https://learn.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-send-dbmail-transact-sql?view=sql-server-ver16

If you want me to write it for ya, please send money... I'm trying to retire. 😀

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

mtz676 SSCrazy Eights Points: 9204 More actions · Answer 7

Have in working and Thank you Jeff !

And happy retirement !! 🙂

This reply was modified 6 months, 2 weeks ago by mtz676.

cyrusbaratt Old Hand Points: 356 More actions · Answer 8

cyrusbaratt

Old Hand

Points: 356

March 21, 2024 at 2:15 pm

#4399526

Thanks Jeff!

DBASupport