February 15, 2011 at 2:46 pm
i ran the trace now after disabling the job through GUI and the below following procs are being executed:
EXEC msdb.dbo.sp_update_job @job_id=N'5bd63372-c981-4cef-b18f-2db4b464ee43',
@enabled=0
EXECUTE @retval = sp_verify_job_identifiers '@job_name',
'@job_id',
@job_name OUTPUT,
@job_id OUTPUT
EXECUTE @retval = msdb.dbo.sp_is_sqlagent_starting
EXECUTE @retval = sp_verify_job @job_id,
@new_name,
@enabled,
@start_step_id,
@category_name,
@owner_sid OUTPUT,
@notify_level_eventlog,
@notify_level_email OUTPUT,
@notify_level_netsend OUTPUT,
@notify_level_page OUTPUT,
@notify_email_operator_name,
@notify_netsend_operator_name,
@notify_page_operator_name,
@delete_level,
@category_id OUTPUT,
@notify_email_operator_id OUTPUT,
@notify_netsend_operator_id OUTPUT,
@notify_page_operator_id OUTPUT,
NULL
UPDATE msdb.dbo.sysjobs
SET name = @new_name,
enabled = @enabled,
description = @description,
start_step_id = @start_step_id,
category_id = @category_id, -- Returned from sp_verify_job
owner_sid = @owner_sid,
notify_level_eventlog = @notify_level_eventlog,
notify_level_email = @notify_level_email,
notify_level_netsend = @notify_level_netsend,
notify_level_page = @notify_level_page,
notify_email_operator_id = @notify_email_operator_id, -- Returned from sp_verify_job
notify_netsend_operator_id = @notify_netsend_operator_id, -- Returned from sp_verify_job
notify_page_operator_id = @notify_page_operator_id, -- Returned from sp_verify_job
delete_level = @delete_level,
version_number = version_number + 1, -- Update the job's version
date_modified = GETDATE() -- Update the job's last-modified information
WHERE (job_id = @job_id)
EXECUTE msdb.dbo.sp_post_msx_operation 'UPDATE', 'JOB', @job_id
EXECUTE msdb.dbo.sp_sqlagent_notify @op_type = N'J',
@job_id = @job_id,
@action_type = N'U'
-- If the name was changed, make SQLServerAgent re-cache any alerts that reference the job
-- since the alert cache contains the job name
EXECUTE @retval = sp_verify_category @class,
@type_in,
NULL,
@category_class OUTPUT,
@category_type OUTPUT
EXECUTE sp_get_composite_job_info @job_id,
@job_type,
@owner_login_name,
@subsystem,
@category_id,
@enabled,
@execution_status,
@date_comparator,
@date_created,
@date_last_modified,
@description
In all these i do not see the place where you mentioned "sp_add_jobserver"? Just an FYI the issue is not there now, if i disabled/enable jobs through GUI works fine now.
February 16, 2011 at 4:40 am
sqldba_icon (2/15/2011)
In all these i do not see the place where you mentioned "sp_add_jobserver"?
He's referring to a step referenced in his BOL link (click on the word in David's post). It's MS's online "how to set up a job using scripts" post.
Viewing 2 posts - 16 through 16 (of 16 total)
You must be logged in to reply to this topic. Login to reply