How to check if the stored procedure is scheduled to run at specific time

Question

In my SQL Server 2008, a stored procedure P_ABC is scheduled to run everyday at 2 a.m.

But how can I verify that this procedure P_ABC is scheduled to run everyday at 2 a.m. or not and it is always running at 2 a.m.? Is there any query to get the lists of procedures that are scheduled to run at specific time?

Are those procedures running under sql server jobs? Or may be there some windows service or something else? — Giorgi Nakeuri, May 17 '15 at 09:33
to the moderator : **requesting to migrate the question to** http://dba.stackexchange.com/ — user4221591, May 17 '15 at 09:41

score 0 · Answer 1 · answered May 17 '15 at 09:21

I don't know of any option to be able to simply query SQL to find out when a stored procedure is run. I have a series of auditing tables to store this information. It took a little bit of time to setup however will give you everything you need.

At a high level you need to do the following within each of your stored procedures:

Call a stored procedure that will allocate a new (incrementing) id for the specific job. This procedure will also store the name of the procedure running (passed as a parameter) and when it started within a table.
At the end of the procedure call another store procedure with the original ID and update the audit record with the end time.

This works well because you know what is run, when it's running and how long it takes.

score 0 · Answer 2 · edited May 23 '17 at 11:45

Not sure this is exactly what you need but you can check this piece of code below.

You can also find it there: SQL Server Find What Jobs Are Running a Procedure

SELECT j.name 
  FROM msdb.dbo.sysjobs AS j
  WHERE EXISTS 
  (
    SELECT 1 FROM msdb.dbo.sysjobsteps AS s
      WHERE s.job_id = j.job_id
      AND s.command LIKE '%procedurename%'
  );

Using this approach you could list all your store procedures (by name) and then find all jobs (or steps to be more specific) that contain these store procedures.

Please refer to this question/answer to list store procedures: Query to list all stored procedures

Thanks

score -1 · Accepted Answer · answered Jun 01 '19 at 05:26

It's too old question but still I am posting this. You can try -

CREATE PROCEDURE MyTask
 AS
BEGIN  
    SET NOCOUNT ON;
    --  For executing the stored procedure at 11:00 P.M
    declare @delayTime nvarchar(50)
    set @delayTime = '23:00'

    while 1 = 1
    begin
        waitfor time @delayTime 
        begin
            --Name for the stored proceduce you want to call on regular bases
            execute [DatabaseName].[dbo].[StoredProcedureName];
        end
    end
END

Then,

-- Sets stored procedure for automatic execution.
sp_procoption    @ProcName = 'MyTask',
                @OptionName = 'startup',
                @OptionValue = 'on'

How to check if the stored procedure is scheduled to run at specific time

3 Answers3