Long running jobs(SQL)

What do you do when you have a job that takes a significant amount of time to process. Well the obvious solution is either improve its performance to speed up the job or wait. When the latter is the only choice you find yourself wanting to know how long the job took or wanting to know when its finished.

This can be achieved fairly easily just by adding alerts into the job. Simple to do and built into the engine, awesome. But what do you do when a job runs continually or to be more precise, runs as soon as it finishes. Maybe you have a reporting job that is constantly needing refreshed data on as fast as an interval as it allows. Maybe you have a log shipping scenario where if you interrupt the entire process it will brake the configuration but preventing it from running again once its finished will not and don't want to make a notification that spams your inbox? What if you need to perform tasks between occurrences of a job that are one off requests? Most of these scenarios would either require diligent supervision and manual interaction.

This is what I wrote SP_Jobstatus for. It waits for a job to finish before returning an output and will hold a query open for as long as needed until the job reaches the desired status. It takes two parameters: @jobname is the freindly name of the sql server job. @WaitForStart is a switch that controls whether you are waiting for the job to start or waiting for it to end.

Create procedure [dbo].[SP_Jobstatus] @job_name nvarchar(max),@waitforstart bit = 0
as

begin

create table #tmpJobStatus (
Job_ID uniqueidentifier
,Last_Run_Date bigint
,Last_Run_Time bigint
,Next_Run_Date bigint
,Next_Run_Time bigint
,Next_Run_Schedule_ID int 
,Requested_To_Run int 
,Request_Source int
,Request_Source_ID varchar(30)
,Running int
,Current_Step int
,Current_Retry_Attempt int
,[State] int)


Declare @var sysname
Declare @job_ID UNIQUEIDENTIFIER 
--set @job_name = 

select @var = SUSER_SNAME()
SET NOCOUNT ON 
EXECUTE msdb..sp_verify_job_identifiers '@job_name',
'@job_id',
@job_name OUTPUT,
@job_id OUTPUT

insert into #tmpJobStatus 
EXECUTE master.dbo.xp_sqlagent_enum_jobs 1 ,@var ,@job_ID

declare @time datetime = getdate()

declare @runstatus int
select @runstatus = Running from #tmpJobStatus
select @runstatus


while((@runstatus !=0 and @waitforstart = 0) or (@runstatus =0 and @waitforstart = 1 and datediff(second,@time,getdate() ) <20))
begin

truncate table #tmpJobStatus 

insert into #tmpJobStatus 
EXECUTE master.dbo.xp_sqlagent_enum_jobs 1 ,@var ,@job_ID 

select @runstatus = Running from #tmpJobStatus 
waitfor delay '00:00:15' 
end 
drop table #tmpJobStatus 
end 

Currently it is using a temp table and truncate for speed but an in memory table var would work as well with some tweeking.