Recently I had a Tweep (read:Twitter users) asking about how to disable a SQL Agent job using the Hash Tag #sqlhelp. The information out there is sketchy but there is a MSDB stored procedure to accomplish this: sp_update_job.

You would need to first verify if this job is currently executing or not.

Use @enabled = 1 to enable the job and to disable the job use @enabled = 0.

Example: To disable a job if it is not currently executing

DECLARE @jobid uniqueidentifier
SET @jobid = '83CE6BD4-80BB-4294-A06E-89BBC2AB8101' -- replace with appropriate job id
CREATE TABLE #tblJobInfo
(job_id                UNIQUEIDENTIFIER NOT NULL,
                            last_run_date         INT              NOT NULL,
                            last_run_time         INT              NOT NULL,
                            next_run_date         INT              NOT NULL,
                            next_run_time         INT              NOT NULL,
                            next_run_schedule_id  INT              NOT NULL,
                            requested_to_run      INT              NOT NULL, -- BOOL
                            request_source        INT              NOT NULL,
                            request_source_id     sysname          COLLATE database_default NULL,
                            running               INT              NOT NULL, -- BOOL
                            current_step          INT              NOT NULL,
                            current_retry_attempt INT              NOT NULL,
                            job_state             INT              NOT NULL
)

INSERT INTO #tblJobInfo
EXECUTE master.dbo.xp_sqlagent_enum_jobs 1,SUSER_SNAME,@jobid

IF ((select [job_state] from #tblJobInfo where Job_ID = @jobid) = 4)
BEGIN
    EXEC msdb..sp_update_job @job_id = @jobid,@enabled = 0
END
DROP TABLE #tblJobInfo

Please note that the above XSP used is undocumented and the script itself will not be supported by CSS in case you run into any issues. This is an AS-IS posting and please test the same in your environment before using it.

Reference:

BOL Topic: sp_update_job

Monitoring Job Activity: http://msdn.microsoft.com/en-us/library/ms188272.aspx