Sql-server – Why does an SQL Server Agent job try to create a procedure

sql serversql-server-agent

I am using SQL Agent to schedule the exeuction of a procedure.

The first time that the procedure is executed, it runs without problems.
The second time the job (step) executes, it does not finish.

A variation of the following SQL statement tells me that the running statement is create procedure xyz... (where xyz is the name of the procedure that the job is supposed to run).

select
   prc.loginame,
   prc.program_name,
   sql.text                                           sql_text
from
   sys.sysprocesses     prc                 cross apply
   sys.dm_exec_sql_text(prc.sql_handle) sql

I don't do any DDL in my procedure (or otherwise) and am wondering what causes the job to try to create the procedure and why the job hangs?

Best Answer

Seeing the procedure create statement popping up is expected behaviour here, it has nothing to do with the job agent itself. And the job step is not recreating the procedure, it is only running it.

Example

If I create this procedure:

CREATE PROC dbo.Waitfordelay
as
WAITFOR DELAY '00:00:10';
SELECT 5;

And then run it in a query window:

EXEC dbo.Waitfordelay;

Then run your query:

loginame       program_name                                    sql_text
Domain\User    Microsoft SQL Server Management Studio - Query  CREATE PROC dbo.Waitfordelay  as  WAITFOR DELAY '00:00:10';  SELECT 5

The create proc also shows up.

If you want more information on the procedure call you could use something like dbcc inputbuffer

dbcc inputbuffer(spid)

For me this returns:

EventType   Parameters  EventInfo
Language Event  0   EXEC dbo.Waitfordelay

Or run sp_whoisactive with the parameter @get_outer_command set to 1.

exec [dbo].[sp_WhoIsActive]
        @get_outer_command = 1;
enter code here

Example result:

dd hh:mm:ss.mss session_id  sql_text                                 sql_command
00 00:00:00.733 54          <?query --WAITFOR DELAY '00:00:10'; --?> <?query --EXEC dbo.Waitfordelay --?>

running sp_whoisactive might also give you more information on what part your procedure is stuck on.

I don't do any DDL in my procedure (or otherwise) and am wondering what causes the job to try to create the procedure and why the job hangs?

The create procedure part is cleared up, the actual problem of the job hanging will be guesswork without having a reproducable example or more information.

Is the job running a query? Can parameter sniffing be an issue here?

What are the wait types when running the job? What is the amount of cpu time / reads?

Running below query (yours adapted) when the jobstep is stuck might give more information:

SELECT
req.total_elapsed_time,
req.cpu_time,
req.reads,
req.last_wait_type
,req.wait_type,
req.wait_time,
sql.text  sql_text
FROM
sys.dm_exec_requests     req                 
cross apply sys.dm_exec_sql_text(req.sql_handle) sql;

We would need more details to know what the actual problem is.

Related Question