5

I have many SQL Server jobs on the server. I need to execute a SP whenever any job execution is done. One hard way is to create a step on every job which calls that SP.

Is there any simple way to do that? Does, SQL Server execute any SP or anything after job completion?

7
  • The SP you are trying to execute, is it the same that needs to be added on next for those multiple sql agent jobs?
    – KASQLDBA
    Commented Feb 12, 2015 at 10:21
  • @Kapil Yes.. I have to execute same SP for each job. It has three parameter 1: Job Name 2: Server Name 3: Status Commented Feb 12, 2015 at 10:30
  • What about using a trigger on the msdb, either the sysjobactivity or sysjobhistory tables might work? I was able to create the trigger, but I didn't test any further. You should be able to do an after insert/update and presumably accomplish running your SP. Commented Feb 12, 2015 at 15:31
  • @Your_Comment_is_not_funny I thought the same but I lack the knowledge of how SQL Server stores information after job completion. If I know the last table which gets updated with the status value then probably I can write a trigger on update. I would appreciate if you can show me how you did that. I can probably test it with different scenarios. Commented Feb 12, 2015 at 20:09
  • @Your_Comment_is_not_funny msdn.microsoft.com/en-us/library/ms189799.aspx says: "Because SQL Server does not support user-defined triggers on system tables, we recommend that you do not create user-defined triggers on system tables."
    – RLF
    Commented Feb 16, 2015 at 18:27

2 Answers 2

1

I have tried to recreate your scenario on a test system using a simple stored procedure. The way I have approached this problem is as follows:

  1. Create a 'StartBackgroundProcess' stored procedure that starts a job named 'BackgroundProcess'.
  2. Create a test stored procedure to be called by the 'BackgroundProcess' job.
  3. Create a 'BackgroundProcess' job that continuously loops, checking for jobs completed and then executes the 'TestSP' stored procedure.
  4. Configure the 'StartBackgroundProcess' stored procedure to be called whenever SQL Server starts.

You can configure the BackgroundProcess to run at any interval within the code and it will check for completed jobs since the last run (ex. check every 30 seconds, 5 minutes, etc.). It then fires a test stored procedure that you can replace with whatever one you were wanting to use.

/*
1. Create a 'StartBackgroundProcess' stored procedure that starts a job named 'BackgroundProcess'.
2. Create a test stored procedure to be called by the 'BackgroundProcess' job.
3. Create a 'BackgroundProcess' job that continuously loops, checking for jobs completed and then executing the 'TestSP' stored procedure.
4. Configure the 'StartBackgroundProcess' stored procedure to be called whenever SQL Server starts.
*/

-- 1. Create the 'StartBackgroundProcess' stored procedure that starts a job named 'BackgroundProcess'.

USE master
GO

IF EXISTS (SELECT * FROM master.dbo.sysobjects o WHERE o.xtype IN ('P') AND o.id = object_id('master.dbo.StartBackgroundProcess'))
DROP PROC StartBackgroundProcess
GO

CREATE PROCEDURE dbo.StartBackgroundProcess
AS
BEGIN
    EXEC msdb.dbo.sp_start_job 'BackgroundProcess'
END
GO


-- 2. Create a test stored procedure to be called by the 'BackgroundProcess' job.

USE Tempdb
GO

CREATE PROCEDURE [dbo].[TestSP]  
AS 
BEGIN
    SELECT 'Running Test', GETDATE()
END
GO


-- 3. Create the 'BackgroundProcess' job that continuously loops, checking for jobs completed and then executing a specified stored procedure.
-- ACTION REQUIRED: Copy/Paste the commented SQL below (up until Step 3) into a new job named 'BackgroundProcess' as a SQL Step and Save. No need to configure a schedule.
-- ACTION REQUIRED: Remove the beginning/ending comment tags from the job!
/* 
USE master
GO

DECLARE @whilecounter AS INT
DECLARE @i AS INT
DECLARE @ScriptToRun varchar(250)
DECLARE @LastRun datetime   
DECLARE @StatementToRun VARCHAR(5000)

IF EXISTS (SELECT * FROM tempdb.dbo.sysobjects o WHERE o.xtype IN ('U') AND o.id = object_id('Tempdb.dbo.BackgroundProcess'))
DROP TABLE Tempdb.dbo.BackgroundProcess

CREATE TABLE Tempdb.dbo.BackgroundProcess
(
    CommandRun VARCHAR(100),
    TimeExecuted DATETIME
)

DECLARE @TimeMarker DATETIME

DECLARE @cmdQueue TABLE
(
    cmd VARCHAR(5000) NOT NULL
)

DECLARE @Runcommand TABLE
(
    cmd VARCHAR(5000) NOT NULL
)

SET @TimeMarker = (SELECT start_execution_date FROM msdb.dbo.sysjobactivity sja INNER JOIN msdb.dbo.sysjobs sj on sja.job_id = sj.job_id WHERE sj.enabled = 1 AND start_execution_date IS NOT NULL AND name = 'BackgroundProcess')

WHILE 1=1
BEGIN
        INSERT @cmdQueue
            SELECT 'EXEC ' + 'TempDB.dbo.TestSP' + ' ' + name + ', ' + @@SERVERNAME FROM msdb.dbo.sysjobactivity sja INNER JOIN msdb.dbo.sysjobs sj on sja.job_id = sj.job_id WHERE sj.enabled = 1 AND start_execution_date IS NOT NULL AND stop_execution_date >= @TimeMarker
        SET @whilecounter = (SELECT COUNT(*) FROM @cmdQueue)
        SET @i = 1
        WHILE @i <= @whilecounter
        BEGIN
            DELETE TOP(1) FROM @cmdQueue
            OUTPUT DELETED.cmd INTO @RunCommand
            INSERT Tempdb.dbo.BackgroundProcess
                SELECT cmd, GETDATE() FROM @RunCommand
            SET @StatementToRun = (SELECT TOP(1) cmd FROM @RunCommand)                      
            EXEC (@StatementToRun)
            DELETE FROM @RunCommand
            SET @i = @i + 1
        END
SET @TimeMarker = GETDATE()
WAITFOR DELAY '000:0:30.000' --Configure this to be the proper frequency for your situation. Ensure it is runs often enough to catch all executions of other jobs.
END
*/

-- 4. Configure the 'StartBackgroundProcess' stored procedure to be called whenever SQL Server starts.

USE master
GO

EXEC sp_procoption 'master.dbo.StartBackgroundProcess', 'startup', 'true' 
GO
1
  • +1 for your answer. Thanks for researching and providing a complete solution. However, I am also able to do it via system triggers but that is not recommended solution on production. So, I will not post another answer which is not suitable for SQL Server health :) Thanks a lot again for your assistance. It helped me Commented Feb 18, 2015 at 12:41
0

You could use an extended event to capture the end of the job. Then have that initiate your desired processing, perhaps via Service Broker and Activation.

1
  • 1
    I was thinking of something along the same lines but wasn't sure of how to hook into a job completed event. Do you have any example of how to do this using extended events?
    – BateTech
    Commented Feb 12, 2015 at 11:33

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.