Job runs but no notification
-
Hello, I have scheduled a job to execute a full back up at four am each morning. It runs fine.
I have Database Mail set up and a test email from my TestAccount which utilizes gmail delivers jsut fine.
But, my completed job isn't triggering a notification. In the properties->notification window of my Full Back up Job properites menu I have checked next to E-mail for 'Action to perform when the job completes', but if I navigate away from it and navigate back it is once again empty.
What steps are necessary between SQL Server Agent Job and Database Mail so that I can trigger a notification to a recipient?
It is a fact that I have a TestAccount but I do not know where to define it's recipients. Intuitively, I know the area of the problem lies here but I don't know how to fix it.
Thanks.
-
I just realized I didn't have the mail profile set to default. So I have changed that.
The remaining question I have is where do I defined the recipients of the default profile? Thank you.
-
- Did you follow the BOL ref "How to: Notify an Operator of Job Status (SQL Server Management Studio)"
- did you create a dbmail profile to be used by sqlagent ?
- are you using a proxy account for the job step(s) ?
- did you set up sqlagent operators ?
- Maybe if you publish your job script, we may be able to help out. (or attach it to your reply)
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
ALZDBA (7/13/2010)
- Did you follow the BOL ref "How to: Notify an Operator of Job Status (SQL Server Management Studio)"- did you create a dbmail profile to be used by sqlagent ?
- are you using a proxy account for the job step(s) ?
- did you set up sqlagent operators ?
- Maybe if you publish your job script, we may be able to help out. (or attach it to your reply)
thank you. The key terms are helping me knock off the problems. So far I have enabled dbmail profile for sql agent and restarted sql agent and am moving on to set up sqlagent operators.
will let you know how it goes.
-
Hi! Per advise, the script is here. I have created the operator "ME!", I have enabled DB Mail, I have made sure to select the operator to notify for the specific job, and restarted SQL Server Agent. Look good to you?
USE [msdb]
GO
/****** Object: Job [AdventureWorks full backup] Script Date: 07/14/2010 09:24:30 ******/
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** Object: JobCategory [Database Maintenance] Script Date: 07/14/2010 09:24:31 ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'Database Maintenance' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'Database Maintenance'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
END
DECLARE @jobId BINARY(16)
EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'AdventureWorks full backup',
@enabled=1,
@notify_level_eventlog=3,
@notify_level_email=3,
@notify_level_netsend=0,
@notify_level_page=0,
@delete_level=0,
@description=N'Automated full backup for AW DB.',
@category_name=N'Database Maintenance',
@owner_login_name=N'WIN-7U1MCSXTJ5Z\Administrator',
@notify_email_operator_name=N'ME!', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object: Step [Full] Script Date: 07/14/2010 09:24:31 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Full',
@step_id=1,
@cmdexec_success_code=0,
@on_success_action=1,
@on_success_step_id=0,
@on_fail_action=2,
@on_fail_step_id=0,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N'TSQL',
@command=N'USE AdventureWorks
GO
BACKUP DATABASE AdventureWorks
TO DISK = N''C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Backup\AdventureWorks_FULL.bak''
WITH INIT, COMPRESSION;
GO',
@database_name=N'AdventureWorks',
@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'Full Backup AdventureWorks',
@enabled=1,
@freq_type=4,
@freq_interval=1,
@freq_subday_type=1,
@freq_subday_interval=0,
@freq_relative_interval=0,
@freq_recurrence_factor=0,
@active_start_date=20100705,
@active_end_date=99991231,
@active_start_time=40000,
@active_end_time=235959,
@schedule_uid=N'2bc29ed2-7c9e-46e9-9b7b-70351c57b168'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:
GO
-
two first impressions :
1) I don't like using windows accounts for job owners ( had to much troubles with that in the early yaers )
Backup jobs are system maintenance jobs, so IMO it is perfectly OK to use a sysadmin SQLUSER or even 'sa' for job owner
2) Point the jobstep to the database you want to use.
To create a backup, there is no need to be actually connected to the target database itself. You can do that from master db, or any other db.
and still here comes number 3:
We always install our db instances in their own set of folders, but never in "c:\program files" if you know what I mean.
Your job works on my sql2008R2 dev edtn system.
USE [msdb]
GO
/****** Object: Job [AdventureWorks full backup] Script Date: 07/14/2010 09:24:30 ******/
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** Object: JobCategory [Database Maintenance] Script Date: 07/14/2010 09:24:31 ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'Database Maintenance' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'Database Maintenance'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
END
DECLARE @jobId BINARY(16)
EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'AdventureWorks full backup',
@enabled=1,
@notify_level_eventlog=3,
@notify_level_email=3,
@notify_level_netsend=0,
@notify_level_page=0,
@delete_level=0,
@description=N'Automated full backup for AW DB.',
@category_name=N'Database Maintenance',
@owner_login_name=N'sa',
@notify_email_operator_name=N'ME!', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object: Step [Full] Script Date: 07/14/2010 09:24:31 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Full',
@step_id=1,
@cmdexec_success_code=0,
@on_success_action=1,
@on_success_step_id=0,
@on_fail_action=2,
@on_fail_step_id=0,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N'TSQL',
@command=N'BACKUP DATABASE AdventureWorks MUST GO_WRONG
TO DISK = N''C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Backup\AdventureWorks_FULL.bak''
WITH INIT, COMPRESSION;
',
@database_name=N'master',
@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'Full Backup AdventureWorks',
@enabled=1,
@freq_type=4,
@freq_interval=1,
@freq_subday_type=1,
@freq_subday_interval=0,
@freq_relative_interval=0,
@freq_recurrence_factor=0,
@active_start_date=20100705,
@active_end_date=99991231,
@active_start_time=40000,
@active_end_time=235959
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:
I configured sqlagent to use the default dbmail profile and provided operator ME! to use my email address.
Off course this job will fail (because I added "MUST GO_WRONG" to the job step command).
i received the mail:
JOB RUN: 'AdventureWorks full backup' was run on 15/07/2010 at 16:32:20
DURATION: 0 hours, 0 minutes, 0 seconds
STATUS: Failed
MESSAGES: The job failed. The Job was invoked by User mydomain\mylogin. The last step to run was step 1 (Full).
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
Thank you, I've noted your observations. I can use my 'sa'. I didn't know I could use master. The paths are in testing environment and I will not put into program files in production.
This script did not run on the morning after I posted the script but it ran this morning. As far as I know I did not restart SQL Server Agent again yesterday so I don't know why it didn't work the day before.
SQL Agents likes to stop running all by itself for some unknown reason.
Thank you for your help in my getting this to work.
-
hxkresl (7/16/2010)
...SQL Agents likes to stop running all by itself for some unknown reason..You should investigate this !
Have a look at the SQLAgent.OUT file, SQLServer's Errorlog file (to be found in your instances \Log folder ) and of course your Windows event log files to see why it stops.
Have a nice weekend.
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
I expanded the SQL Server Agent logs and found "An idle CPU condition has not been defined - OnIdle job schedule will have no effect".
Is it possible that a SQL Agent job wouldn't run because the CPU idle condition was below a certain threshold?
-
the database mail profile can be made
the receipients of that profile can be made by adding the accounts
there u can mention the address receving hte mails..
Regards
Sushant Kumar
MCTS,MCP -
hxkresl (7/17/2010)
I expanded the SQL Server Agent logs and found "An idle CPU condition has not been defined - OnIdle job schedule will have no effect".Is it possible that a SQL Agent job wouldn't run because the CPU idle condition was below a certain threshold?
If you had attached the job to use an "on cpu idle" job schedule.
e.g;
USE [msdb]
GO
/****** Object: Job [test] Script Date: 07/19/2010 09:10:23 ******/
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
DECLARE @jobId BINARY(16)
EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'SSCtest',
@enabled=1,
@notify_level_eventlog=0,
@notify_level_email=0,
@notify_level_netsend=0,
@notify_level_page=0,
@delete_level=0,
@category_name=N'[Uncategorized (Local)]',
@owner_login_name=N'sa', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object: Step [teststep] Script Date: 07/19/2010 09:10:23 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'teststep',
@step_id=1,
@cmdexec_success_code=0,
@on_success_action=1,
@on_success_step_id=0,
@on_fail_action=2,
@on_fail_step_id=0,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N'TSQL',
@command=N'print @@servername',
@database_name=N'master',
@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'JS_OnIdle',
@enabled=1,
@freq_type=128, -- freq_type=128 = on Idle cpu condition
@freq_interval=0,
@freq_subday_type=0,
@freq_subday_interval=0,
@freq_relative_interval=0,
@freq_recurrence_factor=0,
@active_start_date=20100719,
@active_end_date=99991231,
@active_start_time=0,
@active_end_time=235959
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:
GO
This schedule would launch the SSCTest job when "the cpu becomes idle" is met.
You notification would only state this condition isn't configured, so this jobschedule would not occur.
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
hxkresl (7/17/2010)
I expanded the SQL Server Agent logs and found "An idle CPU condition has not been defined - OnIdle job schedule will have no effect".Is it possible that a SQL Agent job wouldn't run because the CPU idle condition was below a certain threshold?
This is really unlikely, it is possible to set jobs to run only when the CPU is idle. If you look in the drop down list of the schedule type of the job schedule then there is an option to run when CPU is idle.
You really wouldn't want a backup to kick off everytime the cpu is idle.
Hope this helps,
Rich[p]
[/p] -
Could you give me the exact path? I don't see a drop down for schedule type within the job itself.
No, I don't plan to run the backup whenever CPU is idle but my concern is that at some point of it being protractedly idle, it won't run the backup job.
-
- Can you start the job using SSMS ?
- is the actual schedule active ?
- double check sqlagent is running.
- can you find something in the sqlagent.out logfile (please post !) or in the job history ?
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
ALZDBA, I am currently not having any problems with the job. Nightly the backups are running. There were only an inexplicable couple of days and all I could find were the CPU idle messages as posted earlier.
There is no remaining question here unless that happens again, so I thank you!
(If you wish to help me with something :-), I am currently struggling very much with permissions, specifically wishing to give a user who has dbowner and public permissions on a database, the addition role of 'databasemailuserrole' so that they can make changes to Database Mail for the database they are responsible for. There is a separate post for this. No one has taken a stab; I hope I presented it properly).
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply