How to disable SQL Server Agent jobs using single script?
-
Hi,
I'm testing the DR scenario (failover & role change) using Log shipping in SQL Server 2005 and we have 20 databases that are involved in Log shipping. So there are 20 backup jobs, 20 copy jobs & 20 Restore Jobs on Secondary instance. In the process of Failover\Role change, we need to DISABLE all backup jobs on Primary and copy & restore jobs on secondary and its taking a lot of time if I disable them one-by-one manually from SQLAGENT.
Is there any script which will disable/enable these jobs in a single shot?
thank you
-
I got the script from below link http://www.mssqltips.com/tip.asp?tip=1400
thanks
-
I'm using the below script to DISABLE log shipping backup jobs:
USE MSDB;
GO
UPDATE MSDB.dbo.sysjobs
SET Enabled = 0
WHERE [Name] LIKE 'LSBackup%';
GO
After executing the above script, backup jobs are showing as DISABLED but still they are running and transaction log is backed up.
But If I disable them manually from SQL Agent, the jobs are NOT running..
Is there any mistake in the above script? why the backups running even after they disabled with the script?
please advice
thanks
-
As far as I'm concerned that script should work!
-
Ignacio A,
I have tested the below script many times.
The jobs are showing as they were disabled in SQLAgent, after executing this script BUT the backups are running as usal.
Is there anything I'm missing??
USE MSDB;
GO
UPDATE MSDB.dbo.sysjobs
SET Enabled = 0
WHERE [Name] LIKE 'LSBackup%';
GO
thanks
-
I'm going to run the script on my test environment. I will let you know what re the results!
-
I forgot to ask you what does the sql agent shows as last run?
-
I have the same problem as you. Disabling the job will not stop the backups and even the last run will show the last backup. I will check if I can find another way to disable the job.
-
The script does not work for enabling the job either. I found another solution, that is using msdb.dbo.sp_update_job. You have to give the job name as a parameter, see the syntax below:
EXEC msdb.dbo.sp_update_job
@job_name=N'LSBackup_test',
@enabled=0
That wil work for sure. The problem is that you have to write it for every job. I hope it helps!
-
You can write a script that generates the commands for every agent job.
DECLARE @cmds TABLE (idINT IDENTITY NOT NULL PRIMARY KEY CLUSTERED,
cmdVARCHAR(MAX))
DECLARE @i INT, @cmd VARCHAR(MAX)
INSERT INTO @cmds (cmd)
SELECT'USE msdb; EXEC dbo.sp_update_job @job_name=''' + [name] + ''', @enabled=0'
FROM msdb.dbo.sysjobs
WHERE enabled = 1
SET @i = @@ROWCOUNT
WHILE @i > 0
BEGIN
SELECT @cmd = cmd, @i = @i - 1
FROM @cmds
WHERE id = @i
RAISERROR(@cmd, 0, 1) WITH NOWAIT
EXEC (@cmd)
END
-
The reason a direct update of the Enabled field doesn't work is that SQL Agent has cached this information and won't see the change until it is restarted. The sp_update_job procedure uses the undocumented procedure sp_sqlagent_notify to make the changes take effect.
-
Nice script! Thank you!
-
So if you disable it with the script an then restart the sqlagent you are good to go!
-
Hi Scott,
DECLARE @cmds TABLE (
id INT IDENTITY NOT NULL PRIMARY KEY CLUSTERED,
cmd VARCHAR(MAX))
DECLARE @i INT, @cmd VARCHAR(MAX)
INSERT INTO @cmds (cmd)
SELECT 'USE msdb; EXEC dbo.sp_update_job @job_name=''' + [name] + ''', @enabled=0'
FROM msdb.dbo.sysjobs
WHERE enabled = 1
SET @i = @@ROWCOUNT
WHILE @i > 0
BEGIN
SELECT @cmd = cmd, @i = @i - 1
FROM @cmds
WHERE id = @i
RAISERROR(@cmd, 0, 1) WITH NOWAIT
EXEC (@cmd)
END
I have executed the above script and its disabling all the jobs and the script working fine. But what should I do if I want to Disable particular jobs only. i.e if there are 10 jobs & I want to disable only 5 jobs?
and also could you please tell me what to be changed to enable the jobs again in the above script?
thanks
-
I just did a server migration last weekend and faced the same issues. Some of the agent jobs are disabled because they are only run manually on demand. You have to have a record of the original job enabled/disabled status before you start the migration. The tables and procedures can be dropped after the new server is running and migration rollback is no longer an option.
-- Save job enabled status before migration-- Run this on the old server, then copy the table to the new server
USE dba
GO
SELECT [name] AS JobName, [enabled]
INTO dbo.MigratedJobStatus
FROM msdb.dbo.sysjobs
--Simplified script to disable all active jobs, regardless of pre-migration status
--This would be run on the old server at the beginning of the migration event
DECLARE @cmd VARCHAR(MAX)
SELECT @cmd = 'USE msdb; '
+ (SELECT 'EXEC dbo.sp_update_job @job_name=''' + [name] + ''', @enabled=0; '
FROM msdb.dbo.sysjobs
WHERE [enabled] = 1
FOR XML PATH(''))
EXEC (@cmd)
GO
--------------------------------------------------------------------------------------------------------
--Procedure to enable all jobs that were active prior to migration
--This would be used on the new server after successful migration
--or on the old server in case of migration rollback
CREATE PROC [dbo].[MigratedJobs_Enable] AS
SET NOCOUNT ON
DECLARE @cmd VARCHAR(MAX)
SELECT @cmd = 'USE msdb; '
+ (SELECT 'EXEC dbo.sp_update_job @job_name=''' + [name] + ''', @enabled=1; '
FROM msdb.dbo.sysjobs
WHERE [enabled] = 0
AND [name] IN (SELECT JobName FROM dbo.MigratedJobStatus WHERE [enabled] = 1)
FOR XML PATH(''))
EXEC (@cmd)
GO
--------------------------------------------------------------------------------------------------------
--Procedure to disable all migrated jobs
--This would be used on the new server in case of migration rollback
CREATE PROC [dbo].[MigratedJobs_Disable] AS
SET NOCOUNT ON
DECLARE @cmd VARCHAR(MAX)
SELECT @cmd = 'USE msdb; '
+ (SELECT 'EXEC dbo.sp_update_job @job_name=''' + [name] + ''', @enabled=0; '
FROM msdb.dbo.sysjobs
WHERE [enabled] = 0
AND [name] IN (SELECT JobName FROM dbo.MigratedJobStatus)
FOR XML PATH(''))
EXEC (@cmd)
GO
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply