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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Pawan KumarDatabase 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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
Powerful Yet Easy-to-Use Network Monitoring

Identify excessive bandwidth utilization or unexpected application traffic with SolarWinds Bandwidth Analyzer Pack.

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
ste5anSenior 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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL

From novice to tech pro — start learning today.