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