Automate notification on DBCC CHECKDB errors.
I am looking for the best way to get notifications if DBCC CHECKDB indicates an error on a database it checks. So lets say I create a maintenance plan that runs CHECKDB at 2AM for all user databases. What is the best method to get notifications that the command reported issues on one of the databases? I don't want to have to scan the logs daily, so is there a standard best practice for getting this?
Thanks.
Answers
- Hi Dave,
Configure a log file for the DBCC job. Create an operator and notify the operator if the job fails. After creating it, in the job steps you need to choose the option to alert the operator in case the job fails.
Configure database mail and you need to enable the mail profile as follows and restart Sql Server agent service refer this link for the same, http://msdn2.microsoft.com/en-us/library/ms186358.aspx. To create an operator in Sql 2005 refer the below link, http://msdn2.microsoft.com/en-us/library/ms175962.aspx
- Deepak
All Replies
- Hi Dave,
Configure a log file for the DBCC job. Create an operator and notify the operator if the job fails. After creating it, in the job steps you need to choose the option to alert the operator in case the job fails.
Configure database mail and you need to enable the mail profile as follows and restart Sql Server agent service refer this link for the same, http://msdn2.microsoft.com/en-us/library/ms186358.aspx. To create an operator in Sql 2005 refer the below link, http://msdn2.microsoft.com/en-us/library/ms175962.aspx
- Deepak So an error reported will result in a the job failing? Since the command completes successfully, but the message contains the error... I did not know if that would fire an alert message.
- Yes. It would result in the job failure and consequently an alert will be fired..
- Deepak I assume it will work in SSIS maintenance plans as well. I will put a Check Database Integrity task component and then have the error ouptut path go to a notify operator component.
Thanks again.
- Its not possible to catch the allocation and consistency errors through a job as these errrors will be the output to the dbcc checkdb command.
If the job which is calling dbcc check db fails due to some x reason then only it will raise an error.
check out the below link for how to automate the checkdb errors including a report file in html format.
http://www.calsql.com/2009/09/automate-dbcc-checkdb_22.html - Its not possible to catch the allocation and consistency errors through a job as these errrors will be the output to the dbcc checkdb command.
If the job which is calling dbcc check db fails due to some x reason then only it will raise an error.
check out the below link for how to automate the checkdb errors including a report file in html format.
http://www.calsql.com/2009/09/automate-dbcc-checkdb_22.html
