Patrons,
A SQL Agent Job running on using with logic using Ola Hallengren for Update Statistics on a daily basis during after hours. But on EVERY SUNDAY the job is either Skipped or Not Run.
Could Anyone advise me to how to solve this problem
Thanks in Advance
Did you confirm that the schedule(s) assigned to the job will run on Sunday? Is the job that runs Ola's stats procedure just a single step, or does it have multiple steps that could terminate early?
Yes previously SUNDAY's were running and it is on Ola's Statistics in ONE Step and there is no multiple steps.
Still unable to understand as there is ANY evidence from Agent Logs but blank as it is not even recorded either.
This is the status. Any help please.
Only thing I can think of is that SQL Agent service is stopped during that time. The SQL Agents logs should show if it starts up again.
Do you have any scheduled tasks on that server that run on Sunday? Anything that would reboot the machine or stop/restart services?
Thanks for your support answer.
on Checking with Agent Log history -- > Unable to send job completion notification email to the operator 'UserDBA' for job 'update Statistics = USER DATABASES'
Prior to this couple of weeks ago it was running fine but on only SUNDAY the completion Notification is DENIED sending to Operator.
-->Should it be understood though the COMPLETION NOTIFICATION not sent but JOB DONE
Notification to the operator is needed (Including Sundays).
Couldn't find any more clue. How to get this done please.
If you're notifying by email, you'll need to speak to whoever administers your mail server. There may be a sending quota or operational hours that limits its use.
Assuming this doesn't happen any other day, can you set up a simple job that does nothing (maybe a PRINT statement) that notifies the same operator and then run it manually to see if it also fails.
Going back to your original post, "skipped or not run" doesn't mean "failed". Does it show in the job history that it failed? Or is there genuinely no history entry when there should be?
We are getting to the solution where it lies perhaps.
Will Check with Manual Process of Email being worked out.
There is NO Job History that says it has FAILED but as Said above the EMAIL HAS NOT SENT.
Thanks for deducing the Error
Error 441 SQLiMail API Send mail failure: ..... The Mail profile is not specidified.....
Checked it.
Can you please let me know how to address this
Many Thanks
What time on Sundays does it run? If in the past it did run on Sundsys and emails were sent successfully, what changed recently? Was fhere any net new thing done in your network such as maintenance window on all servers such as reboots that overlap and collide with send email schedule. If there are no logs at least see if there was a reboot of that server that sends the email.
The fact that it only happens on Sundays smells like some sort of maintenance window you were not informed about or you dismissed and the notification is in some folder in Outlook
Thanks very much for your kind observations ALL.
The Error: Log File Viewer
The Job Failed. The job was invoked by Schedule 10 (Daily). The Last step to run was Step 1 (UPdate Statistics - USER DATABASES). NOTE: Failed to notify 'UserDBA' via Email.
Can you please let me know what is to be probed further.
Thanks aplenty.
Open up operator UserDBA and see if utmmit has a stale or disabled email address
UserDBA Operator is fine on sending Messages on all other days Except on Sundays
Checked though still couldn't find any eveidence
Any Way to get to resolve please
talk to your sysadmin. not being there to vet things out, it is close to impossible to help imho
Yes found in the Log File Viewer on SQL AGENT
[440] SQLMail failure (reason)
[441] SQLiMail API send mail failure (reason: System.InvalidOperationException: The Mail Profile is not specified.
[298] SQL Server Error:14607, Profile name is not valid
[Log] Unable to send job completion notification email to operator 'UserDBA' for job 'UpdateStatistics - Databases'
Based on the above information (errors) perhaps there might be resolution Please.
In Addition to Errors specified..
[000] Configuration option 'Agent XPs' Changed from 0 to 1. Run the RECONFIGURE Statement to Install. [SQLState 01000] (Message 15457)
May be these ERRORS as indicated might deduce Your Kind Help.
Many Thanks
Only GOLDEN QUESTION: why on SUNDAY these above errors are happening and Notification NOT SENDING through Operator : UserDBA
Thanks Perhaps you will give me more Helping Resolution.
Maybe there is a service that is set to manual start instead of automatic.
You are posting the same old error just different flavors
Thanks All who tried to unearth the reasons though still yet to find to find solution
Still not sure if you are doing what is being recommended?
I belive strongly something is being done on the mail server. Check with Admin, which was recommended but not sure if you followed through on that