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?
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 as WAITFOR DELAY '00:00:10'; SELECT 5;
And then run it in a query window:
Then run your query:
loginame program_name sql_text DomainUser 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.
exec [dbo].[sp_WhoIsActive] @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:
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.