Sql-server – Disabling certain steps in a job

jobssql serversql-server-2008-r2sql-server-agent

I have a project coming this weekend where all the jobs has to be stopped but only 1 step in a particular job has to be run.
for eg: i have a a 2 step job and the second step must run and the first step has to be disabled or stopped along with the other jobs.

First of all i thought of stopping the sql server agent, but that will stop all the jobs. Is there any way to do it.

Best Answer

You can't disable a particular job step.

You have two options, the way I see it:

Change the job to have the "start step" as your second step, and have the second step's "on success action" to quit the job (if there are subsequent steps)
Create a separate job with the step's logic and then only use the new job that does the work

Related Solutions

Disabling DBMS_SCHEDULER queue

I can't test it right now but I think you could do this with open/close scheduler window:

first define a window with DBMS_SCHEDULER.create_window. This window could have a 24 hour duration and repeat every day so that it is always open.
modify your job submission so that they run under this window (parameter schedule_name of the DBMS_SCHEDULER.create_job procedure).
when you need to enter maintenance and/or release resource, use DBMS_SCHEDULER.close_window. When your maintenance is done, use DBMS_SCHEDULER.open_window.

The jobs submitted while the window is closed should be queued and run later when the window is opened.

TSQL – Get Job Step Run for All Jobs and Job Steps

This should get you close, if NULL for ProxyName then it's using the SQL Server Agent credentials.

USE msdb

SELECT J.job_id
    , J.name
    , S.step_name
    , S.step_id 
    , P.name AS ProxyName
    , SP.name AS CredentialUserName
    , SP.type_desc AS CredentialUserType
FROM msdb.dbo.sysjobs J
    INNER JOIN msdb.dbo.sysjobsteps S ON S.job_id = J.job_id 
    LEFT OUTER JOIN msdb.dbo.sysproxies P ON P.proxy_id = S.proxy_id
    LEFT OUTER JOIN sys.server_principals SP ON SP.sid = P.user_sid

Best Answer

Related Solutions

Disabling DBMS_SCHEDULER queue

TSQL – Get Job Step Run for All Jobs and Job Steps

Related Question