Enable a SQL Job Unattended

Today a customer asked me to disable a daily load on their test database. I disabled the job but my problem was that I couldn’t enable the job again because I’m only once a week at that customer…
I found this little trick that I want to share with you to activate the daily job unattended.

First of all I scripted the enabling of the “daily job”. Just open the job properties à Check Enabled and hit the Script button.

USE [msdb]
GO

EXEC msdb.dbo.sp_update_job
@job_id=N'bfd08f62-4074-4f83-bebf-c9d630037595',
        @enabled=1

Secondly, I scheduled a new job “Enable daily job” that will activate the daily job the day after it has been disabled.

The job step will be the T-SQL statement that you have scripted.
You need to schedule this job after the daily job time (but has been disabled) but before the next run. Make sure that the Schedule type is set to “One Time” as we only want to activate that job just once 😉

Select Notifications and activate the checkbox “Automatically delete job: When the job succeeds”. We don’t need this “Enable Daily job” anymore once the “Daily job” has been activated.

Easy to configure, the daily job has been enabled unattended and I automatically cleanup my solution. Works like a charm and it is one reminder less in my agenda!

 

Leave a Reply

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