DB email alarms for SQL Server Agent jobs

Hi All,

I am new to SQL Server DB administration and I have 1+ years of experience in SQL DB administration.

I need your help and guidance to  achieve below requirement.

I have created SQL jobs on production SQL DB and I need to set DB email alarms as mentioned below.  I have configured DB email with exchange server login credentials and I am able to receive Test DB emails successfully.

--3 emails per day

1. SQL Index rebuild job: Job runs every day from 2 AM to 5 AM.
I want DB email alarm at 1:45 AM (before start of job saying "Index Rebuild job start at 2 AM)
I want another DB email alarm at 4:30 AM saying "index rebuild job expected completion time is : 5 AM
I want to receive another DB email after completion of job - with job status completed, failed, still Running

--3 emails per day
2. Data Sync job: Job runs every day from 5:30 AM to 6:30 AM.
I want DB email alarm at 5:15 AM (before start of job saying "Data Sync job start at 5:30 AM)
I want another DB email alarm at 6:15 AM saying "Data Sync job expected completion time is : 6:30 AM
I want to receive another DB email after completion of job - with job status completed, failed, still Running

--3 emails per day
3. Full DB Backup job: Job runs every day from 7:00 AM to 8:00 AM.
I want DB email alarm at 6:45 AM (before start of job saying "Full DB Backup job start at 7:00 AM)
I want another DB email alarm at 7:45 AM saying "Full DB Backup job expected completion time is : 8:00 AM
I want to receive another DB email after completion of job - with job status completed, failed, still Running

Please guide me to achieve above DB email alarms with the help of SQL script or  SQL SSMS.
jr SQLDBAAsked:
Who is Participating?
 
Pawan KumarConnect With a Mentor Database ExpertCommented:
You need to follow below steps-

Go to SSMS->go to SQL Server Agent -> Right Click on Jobs -> New Job..Fill all the details EE.jpg->provide the scheduling and re-occurrence  details->Set Alerts..
0
 
jr SQLDBAAuthor Commented:
Hi Pawan,

Can you please tell me how to set the time and message in SSMS for DB email profile.
For ex for Full DB Backup:
I want DB email alarm at 6:45 AM (before start of job saying "Full DB Backup job start at 7:00 AM)
I want another DB email alarm at 7:45 AM saying "Full DB Backup job expected completion time is : 8:00 AM
I want to receive another DB email after completion of job - with job status completed, failed, still Running

1.png2.png
0
 
Pawan KumarDatabase ExpertCommented:
Go to response -> New Operator.
0
Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

 
jr SQLDBAAuthor Commented:
Can you please review and confirm the operator is correct for 6:45 AM (full backup). Do I need to set another two new operator (alarm at 7:45 AM saying "Full DB Backup job expected completion time is : 8:00 AM and another operator for job completion)??

operator for 6:45 AM:
1.png2.png3.png
0
 
ste5anConnect With a Mentor Senior DeveloperCommented:
Just a comment (having 20+ years DB experience):
Please guide me to achieve above DB email alarms with the help of SQL script or  SQL SSMS.
You don't want that. What you want is:

- Get at least those error notifications
Capture.PNG- Get one e-mail after all processes quit running as status report.
1
 
PberSolutions ArchitectCommented:
No comment has been added to this question in more than 21 days, so it is now classified as abandoned.

I have recommended this question be closed as follows:

Split:
-- Pawan Kumar (https:#a42388093)
-- ste5an (https:#a42388200)


If you feel this question should be closed differently, post an objection and the moderators will review all objections and close it as they feel fit. If no one objects, this question will be closed automatically the way described above.

Pber
Experts-Exchange Cleanup Volunteer
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.