Select and Start a SQL Job without knowing the full job name or history

So I was trying to figure out how to get the TLog backup job’s name based on Back History because in this case, the job name happens to vary between SQL Server Instances. Sounds simple enough (well…not quite), then I realized for new SQL Server instances this job might not have even ever ran…so there went that idea.

Instead, I just used dynamic SQL, get the job name and do the work. Here’s the code.

USE MSDB;

DECLARE @TlogJobName nvarchar(max)

SELECT top 1 @TlogJobName = j.name
FROM MSDB.dbo.sysjobs j
INNER JOIN MSDB.dbo.sysjobsteps s ON j.job_id = s.job_id
WHERE j.name LIKE 'Tran%' --Commonly 'Transaction Log Maintenance Plan.Subplan_1'

PRINT 'Starting Sql Job: '+@TlogJobName

EXEC msdb.dbo.sp_start_Job @job_name = @TlogJobName;
WAITFOR DELAY '00:01:00' --Wait 1 minute for job to finish.

/*Check job histry should be done by now.*/
SELECT  j.name AS 'JobName',
msdb.dbo.agent_datetime(run_date, run_time) AS 'RunDateTime',
h.run_status --(1=SUCCESS)
FROM msdb.dbo.sysjobs j
INNER JOIN msdb.dbo.sysjobsteps s ON j.job_id = s.job_id
INNER JOIN msdb.dbo.sysjobhistory h ON s.job_id = h.job_id
WHERE j.enabled = 1
AND j.name = @TlogJobName
AND s.step_id = h.step_id
AND h.step_id <> 0
ORDER BY JobName, RunDateTime DESC

Hope you find it useful as well.

Thanks,
Hiram

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.