Hi,

Can anyone tell me if it's possible to include the error log of the job in the SQL DB Mail message?

At the moment I get notified that a job has failed, but still have to log onto the server to look at the 'View History' section of the job to see why it failed.

If I could include this info in the email, it would let me prioritize these messages.

Thanks

I hope you are aware of How to setup database mail.

If not follow this link http://www.mssqltips.com/tip.asp?tip=1438

After completion.

Question- Would like to get Email failed backup alert with Cause, Right?

Solution-

Create a Backup Job I guess you already created.

Configure Notifications (Job Properties) select your Email on completion.

(Your Completion job is configured now you will get the Successful notification.)

Create An Alert

(On the event of Failure, Log will generate with error no. Lets Say.3041)

Configure with Response / and In option you will get the include error text inSelect E-mail.

Its done.

If job will successful- You will get a Successful message mail.

If job Fails - You will get an Mail with Possible Couse in .txt attached with mail.

Good Luck

BR
Praveen Barath

Mark as answer if it helps

Hi Praveen,

Thanks that is very useful.
I am just having some problems setting up the alert. Can you tell me the configuration required, so that It will alert me if the job fails.

After giving the alert a name, please specify the best settings to allow this under general.

I have setup Response to notify operator, and options to include alert error text in email.

Thanks

Hi,

Please follow the steps for configration,

1. In Object Explorer, connect to an instance of the SQL Server Database Engine, and then expand that instance.

2. Expand SQL Server Agent.

3. Right-click Alerts and then click New Alert.

4. In the Name box, enter a name for this alert.

5. Check the Enable check box to enable the alert to run. By default, Enable is checked.

6. In the Type box, click SQL Server event alert.

7. In the Database name list, select a database to restrict the alert to a specific database.

8. Click Error number, and then type a valid error number for the alert.

9. To restrict the alert to a particular character sequence, check the box corresponding to Raise alert whenmessagecontains, and enter a keyword or character string for the Message text. The maximum number of characters is 100.

   How to: Define the Response to an Alert (SQL Server Management Studio)

If this doesnt help please elobrate the problem.

BR
Praveen Barath

Mark as answer if it helps.

Hi Praveen,

Still not getting the message.

These are my setttings. I just want to be notified whenever any SQL Job fails.

General: Name:Alert Test

Type: SQL Server event alert

Database name: <all dartabases>

Error number: Left it unselected as I want to be notified if all jobs fail for any reason.

Severity: 001-Misc System information

Raise alert when message contains: Message text: The job failed

Response: Selected notify operators, and checked email box

Options: Include alert error text in email.

I ran a job which fails, but I do not get the alert message. I noticed that I get a Notification message from SQL DB Mail, but the alert does not work?


Hope you can advise

Example-

EXEC msdb.dbo.sp_add_alert @name=N'Alert_Test,

@message_id=3041,

@severity=0,

@enabled=1,

@delay_between_responses=0,

@include_event_description_in=1,

@notification_message=N'Backup failed for ServerName',

@category_name=N'[Uncategorized]',

@job_id=N'51c2cb58-b542-414e-837c-c99828ead41b'

You are doing everything fine, But you need to mention the Message ID- '3041'.

Lets say you would like to create an alert for Backup Failed. Mention an ID 3041.Every backup failed with this errror it is not diffrent for diffrent database.

Code SnippetThese are my setttings. I just want to be notified whenever any SQL Job fails.

If you would like to notify for all jobs, its better to define in jobs as i mentioned previously.(notify operator by email , But not with error log text.)

TIP- Good idea is to run the job in test envirnment and then capture the Error log ID.Then create an Alert for all for Error log Text.

BR
Praveen Barath

Thanks,

Yes I thought I would have to enter an error number, which is a bit of a problem as I just want to get an email with error log if any job fails. I have around 30 jobs at some sites and would need to know that in the event that any of them fail, I will get the error email regardless of what the error was.

I will have to try and find a work around. Perhaps it is possible to query the MSDB database and return that as an attached result.

Not too sure though.

Thanks for your help anyway.

Hey try to understand that in job notification there is no option as such you would get the Attached file with error log.

Alert can do.

If you would like to read the error log then try WMI query. Search on net you will find it.

Use Ful Tip -You can get the information by executing

Code Snippet

Use MSDB

select * from dbo.sysjobhistory

Code SnippetExec master..sp_readerrorlog

You can check under Run_status column. I guess trigger on table can solve your purpose but creating trigger on system tables are NOT RECOMMANDED.

BR
Praveen Barath

Mark As Answer If it Helps

Also try this on test ...

Select Database

Code Snippet

Create Table #ErrorLog (LogID int identity(1, 1) not null primary key,

LogText nvarchar(4000) null,

ContinuationRow INT null)

Insert into #ErrorLog

Exec master..sp_readerrorlog 1

Run

Code SnippetSelect * From #ErrorLog WHERE LogText LIKE 'ERROR:%'

Ref-http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/Q_22940546.html

BR
Praveen Barath

Hi Praveen,

Thanks for your help.
I'm getting there slowly.
I've worked out which tables to query in the msdb and I have got the

EXEC msdb.dbo.sp_send_dbmail working ok to email me a query as a .txt attachment.

I was just checking that I can't edit the way DB Mail sends notifications to add the

@attach_query_result_as_file, so that it will then send me the notification along with a .txt file query of the msdb tables?

Was just a thought?

Thanks again so far

Great

good luck.

Mark as answer if it helps.

BR
Praveen Barath

I had the same question...   I found this little number.  Let me tell you.  It works wonderfully.  Just needed to supply the SMTP server name.  I ran this across ALL my instances, and I have SQL 2005, 2008, and 2012 SQL Instances.  Works across them all np.  This guy deserves and award for finally making something like this.

According to the instructions... I take the script here:
https://mikesdatawork.wordpress.com/2015/07/06/sql-email-alerts-with-database-mail/

Then placed it into a Job called:  SEND SQL JOB ALERTS (beta)   Literally; all I did was create the job with the name:  SEND SQL JOB ALERTS (beta)  Then I pasted that massive script in there.

Then all you need to do is create a trigger found here:
https://mikesdatawork.wordpress.com/2015/07/15/create-a-trigger-on-the-sysjobhistory-table-to-execute-a-job/

Thats it.

Although I did drop the "...(beta)" suffix from the Job, and trigger.  Now it just:  SEND SQL JOB ALERTS