Stopping a Series of SQL Agent Jobs

4

October 28, 2011 by Mike Hillwig

Yesterday, I participated in my first DR test at this company. It was a long day, but we learned a lot. This was also our first SQL Server client that we tested. For our Oracle clients, the Oracle DBAs have this process down to a science.

We’re using Log Shipping for our DR environment, and before I opened up the databases, I needed to stop the LS Restore processes for all 22 of the databases, and I wasn’t about to right-click on 22 SQL Agent jobs. That’s when I wrote this little nugget:

set nocount on
go
select 'EXEC msdb.dbo.sp_update_job @job_id=N'''+ cast(job_id as varchar(60))
+''', @enabled=0
GO'
from msdb.dbo.sysjobs
where name like 'LSRestore_%'
and enabled = 1

What this does is generate a SQL script that I can copy, paste, and run. Sure, I could have encapsulated this into a cursor and EXECute it, but I like to have a little bit of control over these things.  Lately, I’ve become a big fan of writing scripts that generate other scripts.

After the DR test, I write something similar that re-enabled those jobs once I had my backups restored.

4 thoughts on “Stopping a Series of SQL Agent Jobs

  1. Derek Wharton says:

    Great article! I find myself generating sql scripts more & more. I use something similar to make sure my jobs have the correct owner and that they all have the notify operator set.

  2. Rajkumar says:

    By seeing this code
    set nocount on
    go
    select ‘EXEC msdb.dbo.sp_update_job @job_id=N”’+ cast(job_id as varchar(60))

    +”’, @enabled=0
    GO’
    from msdb.dbo.sysjobs
    where name like ‘LSRestore_%’
    and enabled = 1
    I am been surprised and I am happy by seeing you in this way.

    I know the Java/j2ee, sql server 2005,2008, linux, android.
    So can you help me to get a software job.If so you can help me then you can immediately my phone number 9000618476 or i can call you by giving me the number.

  3. […] Stopping a Series of SQL Agent Jobs – A handy T-SQL snippet from Mike Hilwig (Blog|Twitter). […]

  4. Lavonne says:

    I see you share interesting stuff here, you can earn some additional money, your blog has huge potential, for
    the monetizing method, just search in google
    – K2 advices how to monetize a website

Leave a Reply

Your email address will not be published. Required fields are marked *