SQL Server Email Notifications during Automated Backup

Hi,

Automated backups - are you using a maintenance plan?

One way is to find the job, and put a notification on the job.
Are you set up as an operator?

Steps
Set up DBMail
Set up an operator and test.
Add a notification on the job

You shoudl now get an email when the job finishes ...

HTH
  David

Yes, I am using the Maintenance Plans.

Hi,

Have you got DBMail setup? Have you got operators setup in SQL?

Cheers
  David

I don't think I have DBMail setup. What are operators in SQL?

I have an Operator created and I think I have the DB Mail configured correctly. How do I tie this to email notifications for a Maintenance Plan?

I have this working now. When a task fails, I have  a Notify Operator Task executed that emails me that the task is failed. When a task is successful, I have  a Notify Operator Task executed that emails me that the task completed with success.

How to do I configure a Notify Operator Task to email when the backup fails or succeeds as a whole? It seems like the Notify Operator task is tied to one task. This is fine, but I have 5 other tasks in the plan. I can add Notify Operator Tasks for each step in the plan, but that would be many Operator Tasks in the plan. What do you recommend?

Hi,

For the SQL job that runs this, can you get a notification?

I'm not sure about your terminology here, where you have separate tasks to email.

What I'm suggesting is to go back tothe beginning:
Make sure database mail is installed correctly and working. From what you write, it likely is.
Create an operator in SQL Agent. Check you can email this operator.
On the SQL Agent job(s), create a notification.

The attachment shows the notifications tab from a job, I've highlighted the tab name, the check box to email, the operator selection - blank in this case - and the when job completes rather than fails or succeeds.

HTH
  David
2009-03-07-Job-Properties.bmp

I got everything working. When the maintenance plan finishes, SQL sent me an email. Also, have a Notify Operator task for when a task fails.

My question is I have 7 steps in my plan. Removing old stats, red-oing indexes, creating a BAK, shrinking the database, cleaning txt files and such. Do I need to put a Notify Operator task on all of these? I just have set on cleaning txt and bak files, which is the last step. May just have Notify Operator task on the BAK, since this is the most important one.

Hi,

Be careful with shrinking the database. I wouldn't automate that on a production system. I do that when restoring production to test on the log files, as presumably test and development wont need large log files.

If you are overly heavy with shrinking the database and/or database files, firstly there is a lot of work to do that; secondly the database is going to grow again so the OS will fragment the datafile; thirdly database growth is relatively quite expensive when a single line insert is waiting on another page (8k) or extent (64k) and your multi-terra byte database is growing by 10 percent.

A bit of an overstatement:
I've seen AngelIII strongly suggest not to shrink the database when that space will be consumed in the next 6 months.

HTH
  David