Update SQL Server Agent Job Owner

Hi there,

I have been working on a project to move/migrate lots of jobs to a new server in another domain and we had to update quite a bit of jobs to set the owner to 'sa' prior to migration.  Out of close to 600 jobs, there were about 200 jobs owned by not 'sa' but an account in Active Directory.  So instead of touch all of those jobs manually and change the owner I come up with the following script and all done in less than 10 minutes including time took come up with the script.  I decided to share it here and also use my blog to document it if I ever need it.

HTH,
Bulent

USE MSDB;
GO
SET NOCOUNT ON;
/********************************

This script is used to update the job owner to sa for
the jobs that is not already owned by sa.

********************************/
DECLARE
      @MinRowId INT = 1
    , @MaxRowId INT
    , @jobId UNIQUEIDENTIFIER
    , @owner_login_name NVARCHAR(128) = N'sa';

-- Store the job steps in a temp table to update the in the loop later
IF OBJECT_ID('tempdb..#Jobs') IS NOT NULL
    DROP TABLE #Jobs;
CREATE TABLE #Jobs (
      RowId INT IDENTITY (1,1) NOT NULL
    , job_id UNIQUEIDENTIFIER NOT NULL
    , jobname SYSNAME NOT NULL
    , owner_sid VARBINARY(85)
    );
-- Insert list of jobs not owned by 'sa'
-- Can change the select statement and
-- where clause to your need
INSERT INTO #Jobs (job_id, jobname, owner_sid )
SELECT    j.job_id, j.name, j.owner_sid
FROM    dbo.sysjobs AS j
WHERE    J.owner_sid <> 0x01 -- sid for SA
ORDER BY j.name

SET    @MaxRowId = @@ROWCOUNT;

WHILE @MinRowId <= @MaxRowId
    BEGIN
        SELECT    @jobId = job_id
        FROM    #Jobs
        WHERE    RowId = @MinRowId;
       
        EXEC sp_update_job @job_id = @jobId, @owner_login_name = @owner_login_name;

        SET @MinRowId = @MinRowId + 1;
    END
--Return list of jobs and original owner sid
SELECT * FROM #Jobs;
DROP TABLE #Jobs;

Find Actively Running SQL Server Agent Jobs

Hi there,

An ETL developer asked my help recently with a SQL Server Agent job needed to start running if the main job finished running.  Occasionally main job run longer than expected and interfered with the secondary job which should run after the main job.  In the first step of secondary job we added a step to continue if the following query returned no rows if it returns a row then we set the job to retry again in 10  minutes for 3 times which solved our issue.

HTH,
Bulent


USE msdb;
GO
SELECT
    j.name AS job_name,
    ja.start_execution_date,     
    ISNULL(last_executed_step_id,0)+1 AS current_executed_step_id,
    Js.step_name
FROM dbo.sysjobactivity AS ja
    LEFT OUTER JOIN dbo.sysjobhistory AS jh ON ja.job_history_id = jh.instance_id
    INNER JOIN dbo.sysjobs AS j ON ja.job_id = j.job_id
    INNER JOIN dbo.sysjobsteps AS js ON ja.job_id = js.job_id AND ISNULL(ja.last_executed_step_id,0)+1 = js.step_id
WHERE ja.session_id = (SELECT TOP 1 session_id FROM msdb.dbo.syssessions ORDER BY agent_start_date DESC)
AND ja.start_execution_date is not null
AND ja.stop_execution_date is null
--AND j.name = 'NameOfTheJob' -- Uncomment and supply the name of the job you need to find out if it's actively running
GO