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.
If I create this procedure:
CREATE PROC dbo.Waitfordelay
WAITFOR DELAY '00:00:10';
And then run it in a query window:
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
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.
@get_outer_command = 1;
enter code here
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
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:
cross apply sys.dm_exec_sql_text(req.sql_handle) sql;
We would need more details to know what the actual problem is.