Monday, February 27, 2012

Delete/Disable Multiple SQL Server jobs

DECLARE @jobId binary(16),
@PK_Job INT,
@Counter TINYINT

DECLARE @Jobs TABLE
(
PK_Job INT IDENTITY(1,1),
job_id BINARY(16)
)
BEGIN
INSERT INTO @Jobs
(
job_id
)
SELECT job_id FROM msdb.dbo.sysjobs

SET @PK_Job = 0

WHILE 1 = 1
BEGIN
---- Looping through @Jobs Table
SELECT
TOP 1 @jobId = job_id,
@PK_Job = PK_Job
FROM
@Jobs
WHERE
PK_Job > @PK_Job
ORDER BY
PK_Job ASC

SELECT @Counter = @@ROWCOUNT
IF @Counter = 0
BREAK

IF (@jobId IS NOT NULL)
BEGIN
--For Disable un-comment the below code
--EXEC msdb.dbo.sp_update_job @jobId, @enabled = 0

--For Delete un-comment the below code
--EXEC msdb.dbo.sp_delete_job @jobId
PRINT @jobId
END
END

END

No comments:

Post a Comment