修改msdb数据库中的过程sp_delete_job,如下：
USE [msdb] GO /****** Object: StoredProcedure [dbo].[sp_delete_job] Script Date: 2014/1/15 9:56:01 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER OFF GO ALTER PROCEDURE [dbo].[sp_delete_job] @job_id UNIQUEIDENTIFIER = NULL, -- If provided should NOT also provide job_name @job_name sysname = NULL, -- If provided should NOT also provide job_id @originating_server sysname = NULL, -- Reserved (used by SQLAgent) @delete_history BIT = 1, -- Reserved (used by SQLAgent) @delete_unused_schedule BIT = 1 -- For backward compatibility schedules are deleted by default if they are not -- being used by another job. With the introduction of reusable schedules in V9 -- callers should set this to 0 so the schedule will be preserved for reuse. AS BEGIN DECLARE @current_msx_server sysname DECLARE @bMSX_job BIT DECLARE @retval INT DECLARE @local_machine_name sysname DECLARE @category_id INT DECLARE @job_owner_sid VARBINARY(85) SET NOCOUNT ON -- Remove any leading/trailing spaces from parameters SELECT @originating_server = UPPER(LTRIM(RTRIM(@originating_server))) -- Turn [nullable] empty string parameters into NULLs IF (@originating_server = N'') SELECT @originating_server = NULL -- Change server name to always reflect real servername or servername\instancename IF (@originating_server IS NOT NULL AND @originating_server = '(LOCAL)') SELECT @originating_server = UPPER(CONVERT(sysname, SERVERPROPERTY('ServerName'))) IF ((@job_id IS NOT NULL) OR (@job_name IS NOT NULL)) BEGIN EXECUTE @retval = sp_verify_job_identifiers '@job_name', '@job_id', @job_name OUTPUT, @job_id OUTPUT, @owner_sid = @job_owner_sid OUTPUT IF (@retval <> 0) RETURN(1) -- Failure END -- We need either a job name or a server name, not both IF ((@job_name IS NULL) AND (@originating_server IS NULL)) OR ((@job_name IS NOT NULL) AND (@originating_server IS NOT NULL)) BEGIN RAISERROR(14279, -1, -1) RETURN(1) -- Failure END -- Get category to see if it is a misc. replication agent. @category_id will be -- NULL if there is no @job_id. select @category_id = category_id from msdb.dbo.sysjobs where job_id = @job_id -- If job name was given, determine if the job is from an MSX IF (@job_id IS NOT NULL) BEGIN SELECT @bMSX_job = CASE UPPER(originating_server) WHEN UPPER(CONVERT(sysname, SERVERPROPERTY('ServerName'))) THEN 0 ELSE 1 END FROM msdb.dbo.sysjobs_view WHERE (job_id = @job_id) END -- If server name was given, warn user if different from current MSX IF (@originating_server IS NOT NULL) BEGIN EXECUTE @retval = master.dbo.xp_getnetname @local_machine_name OUTPUT IF (@retval <> 0) RETURN(1) -- Failure IF ((@originating_server = UPPER(CONVERT(sysname, SERVERPROPERTY('ServerName')))) OR (@originating_server = UPPER(@local_machine_name))) SELECT @originating_server = UPPER(CONVERT(sysname, SERVERPROPERTY('ServerName'))) EXECUTE master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent', N'MSXServerName', @current_msx_server OUTPUT, N'no_output' SELECT @current_msx_server = UPPER(@current_msx_server) -- If server name was given but it's not the current MSX, print a warning SELECT @current_msx_server = LTRIM(RTRIM(@current_msx_server)) IF ((@current_msx_server IS NOT NULL) AND (@current_msx_server <> N'') AND (@originating_server <> @current_msx_server)) RAISERROR(14224, 0, 1, @current_msx_server) END -- Check authority (only SQLServerAgent can delete a non-local job) IF (((@originating_server IS NOT NULL) AND (@originating_server <> UPPER(CONVERT(sysname, SERVERPROPERTY('ServerName'))))) OR (@bMSX_job = 1)) AND (PROGRAM_NAME() NOT LIKE N'SQLAgent%') BEGIN RAISERROR(14274, -1, -1) RETURN(1) -- Failure END -- Check permissions beyond what's checked by the sysjobs_view -- SQLAgentReader and SQLAgentOperator roles that can see all jobs -- cannot delete jobs they do not own IF (@job_id IS NOT NULL) BEGIN IF (@job_owner_sid <> SUSER_SID() -- does not own the job AND (ISNULL(IS_SRVROLEMEMBER(N'sysadmin'), 0) <> 1)) -- is not sysadmin BEGIN RAISERROR(14525, -1, -1); RETURN(1) -- Failure END END -- Do the delete (for a specific job) IF (@job_id IS NOT NULL) BEGIN -- Note: This temp table is referenced by msdb.dbo.sp_delete_job_references, -- so it cannot be declared as a local table. CREATE TABLE #temp_jobs_to_delete (job_id UNIQUEIDENTIFIER NOT NULL PRIMARY KEY CLUSTERED, job_is_cached INT NOT NULL) DECLARE @temp_schedules_to_delete TABLE (schedule_id INT NOT NULL) INSERT INTO #temp_jobs_to_delete SELECT job_id, (SELECT COUNT(*) FROM msdb.dbo.sysjobservers WHERE (job_id = @job_id) AND (server_id = 0)) FROM msdb.dbo.sysjobs_view WHERE (job_id = @job_id) -- Check if we have any work to do IF (NOT EXISTS (SELECT * FROM #temp_jobs_to_delete)) BEGIN DROP TABLE #temp_jobs_to_delete RETURN(0) -- Success END -- Post the delete to any target servers (need to do this BEFORE -- deleting the job itself, but AFTER clearing all all pending -- download instructions). Note that if the job is NOT a -- multi-server job then sp_post_msx_operation will catch this and -- will do nothing. Since it will do nothing that is why we need -- to NOT delete any pending delete requests, because that delete -- request might have been for the last target server and thus -- this job isn't a multi-server job anymore so posting the global -- delete would do nothing. DELETE FROM msdb.dbo.sysdownloadlist WHERE (object_id = @job_id) and (operation_code != 3) -- Delete EXECUTE msdb.dbo.sp_post_msx_operation 'DELETE', 'JOB', @job_id -- Must do this before deleting the job itself since sp_sqlagent_notify does a lookup on sysjobs_view -- Note: Don't notify agent in this call. It is done after the transaction is committed -- just in case this job is in the process of deleting itself EXECUTE msdb.dbo.sp_delete_job_references @notify_sqlagent = 0 -- Delete all traces of the job BEGIN TRANSACTION DECLARE @err int --Get the schedules to delete before deleting records from sysjobschedules IF(@delete_unused_schedule = 1) BEGIN --Get the list of schedules to delete INSERT INTO @temp_schedules_to_delete SELECT DISTINCT schedule_id FROM msdb.dbo.sysschedules WHERE (schedule_id IN (SELECT schedule_id FROM msdb.dbo.sysjobschedules WHERE (job_id = @job_id))) END
--新增代码 DELETE a FROM msdb.dbo.sysmaintplan_log a JOIN sysmaintplan_subplans b on a.subplan_id=b.subplan_id JOIN #temp_jobs_to_delete c on b.job_id=c.job_id
--新增代码 DELETE b FROM sysmaintplan_subplans b JOIN #temp_jobs_to_delete c on b.job_id=c.job_id DELETE FROM msdb.dbo.sysjobschedules WHERE job_id IN (SELECT job_id FROM #temp_jobs_to_delete) DELETE FROM msdb.dbo.sysjobservers WHERE job_id IN (SELECT job_id FROM #temp_jobs_to_delete) DELETE FROM msdb.dbo.sysjobsteps WHERE job_id IN (SELECT job_id FROM #temp_jobs_to_delete) DELETE FROM msdb.dbo.sysjobs WHERE job_id IN (SELECT job_id FROM #temp_jobs_to_delete) SELECT @err = @@ERROR IF @err <> 0 BEGIN ROLLBACK TRANSACTION RETURN @err END --Delete the schedule(s) if requested to and it isn't being used by other jobs IF(@delete_unused_schedule = 1) BEGIN --Now OK to delete the schedule DELETE FROM msdb.dbo.sysschedules WHERE schedule_id IN (SELECT schedule_id FROM @temp_schedules_to_delete as sdel WHERE NOT EXISTS(SELECT * FROM msdb.dbo.sysjobschedules AS js WHERE (js.schedule_id = sdel.schedule_id))) END -- Delete the job history if requested IF (@delete_history = 1) BEGIN DELETE FROM msdb.dbo.sysjobhistory WHERE job_id IN (SELECT job_id FROM #temp_jobs_to_delete) END -- All done COMMIT TRANSACTION -- Now notify agent to delete the job. IF(EXISTS(SELECT * FROM #temp_jobs_to_delete WHERE job_is_cached > 0)) BEGIN DECLARE @nt_user_name NVARCHAR(100) SELECT @nt_user_name = ISNULL(NT_CLIENT(), ISNULL(SUSER_SNAME(), FORMATMESSAGE(14205))) --Call the xp directly. sp_sqlagent_notify checks sysjobs_view and the record has already been deleted EXEC master.dbo.xp_sqlagent_notify N'J', @job_id, 0, 0, N'D', @nt_user_name, 1, @@trancount, NULL, NULL END END ELSE -- Do the delete (for all jobs originating from the specific server) IF (@originating_server IS NOT NULL) BEGIN EXECUTE msdb.dbo.sp_delete_all_msx_jobs @msx_server = @originating_server -- NOTE: In this case there is no need to propagate the delete via sp_post_msx_operation -- since this type of delete is only ever performed on a TSX. END IF (OBJECT_ID(N'tempdb.dbo.#temp_jobs_to_delete', 'U') IS NOT NULL) DROP TABLE #temp_jobs_to_delete RETURN(0) -- 0 means success END
