Thursday, April 23, 2015

Notification when a group of jobs complete

I have a large number of jobs that I run using a cursor (All jobs start with PS_ and each job loads a single table from ORACLE) so they can run in parallel.  I want to be able to be notified when the last job completes.  Should this done using a trigger?  Or should I just add a loop statement to the stored procedure that fires this job?  I know which individual takes the longest and I could simply just add a notification to send email on success but I want it to be more robust.  Below is what I am using to trigger the jobs.

Thanks in advance!

BEGIN
SET NOCOUNT ON;
                                
        DECLARE @year CHAR(4)
        DECLARE @month CHAR(2)
        DECLARE @day CHAR(2)

        SET @year = CAST(DATEPART(YEAR, GETDATE()) AS VARCHAR)
        SET @month = CASE WHEN LEN(CAST(DATEPART(MONTH, GETDATE()) AS VARCHAR)) = 1
                          THEN '0'+ CAST(DATEPART(MONTH, GETDATE()) AS VARCHAR)
                          ELSE CAST(DATEPART(MONTH, GETDATE()) AS VARCHAR)
                     END

        SET @day = CASE WHEN LEN(CAST(DATEPART(DAY, GETDATE()) AS VARCHAR)) = 1
                        THEN '0' + CAST(DATEPART(DAY, GETDATE()) AS VARCHAR)
                        ELSE CAST(DATEPART(DAY, GETDATE()) AS VARCHAR)
                   END



        DECLARE @job_name NVARCHAR(255)


        DECLARE cur CURSOR
        FOR
            SELECT  name
            FROM    msdb.dbo.sysjobs X
            WHERE   SUBSTRING(X.name, 1, 3) = 'PS_'
        OPEN cur

        FETCH NEXT FROM cur INTO @job_name
        WHILE @@fetch_status = 0
            BEGIN
                CURSOR_START:
                                                                        --if the job has not completed          
                IF ( SELECT COUNT(*)
                     FROM   sysjobhistory
                     WHERE  job_id = ( SELECT   job_id
                                       FROM     sysjobs
                                       WHERE    name = @job_name
                                     )
                            AND step_id = 0 --job outcome
                            AND run_status = 1 --0 = failed 1 = success
                            AND run_date = @year + @month + @day
                   ) = 0
                    AND
                                        --and the job is not currently running
                    ( SELECT    COUNT(*)
                      FROM      sysjobactivity
                      WHERE     job_id = ( SELECT   job_id
                                           FROM     sysjobs
                                           WHERE    name = @job_name
                                         )
                                AND CONVERT(VARCHAR(10), start_execution_date, 111) = CONVERT(VARCHAR(10), GETDATE(), 111)
                                AND stop_execution_date IS NULL
                    ) = 0
                    BEGIN
                        IF ( SELECT    COUNT(*)
                      FROM      sysjobactivity a1
                                          INNER JOIN sysjobs a2 ON a2.job_id = a1.job_id
                      WHERE     CONVERT(VARCHAR(10), start_execution_date, 111) = CONVERT(VARCHAR(10), GETDATE(), 111)
                                AND stop_execution_date IS NULL
                                                                AND SUBSTRING(name, 1, 3) = 'PS_'
                           ) < 10
                            BEGIN
                                PRINT 'EXEC msdb.dbo.sp_start_job @job_Name='
                                    + CAST(@job_name AS VARCHAR)
                                                                EXEC msdb.dbo.sp_start_job @job_Name=@job_Name
                                                                WAITFOR DELAY '00:00:10'
                            END
                        ELSE
                            BEGIN
                                PRINT 'WAIT 10 jobs already running:  EXEC msdb.dbo.sp_start_job @job_Name='
                                    + CAST(@job_name AS VARCHAR)
                                WAITFOR DELAY '00:00:10'
                                GOTO CURSOR_START

                            END
                    END
                ELSE
                    BEGIN
                        PRINT CAST(@job_name AS VARCHAR)
                            + ' The job is already currently running or completed sucessfully today'
                    END
                                                
                    
                FETCH NEXT FROM cur INTO @job_name
            END
        CLOSE cur
        DEALLOCATE cur
END                                             

No comments:

Post a Comment