SQL Job running long. Need timing for each stored proc running.
Hi, I have a job that just started running long. There is only 1 step which executes a stored procedure. That stored procedure kicks off multiple stored procedures when processing. I'm trying to pinpoint which stored proc is taking so long. What is the best way to determine length of time for each? It runs off hours so I can't execute manually. Thanks!
@caoneill You can start the SQL Server Profiler trace, including appropriate events and filters. For example, select SQL: BatchCompleted and SQL: BatchStarted for events, un-check all other events. Optionally, add appropriate filter(s) so that only the procedures executed by the job are traced. The profile trace window will give you the information about every stored procedure executed from the job. The Duration column of the batch completed events will display the execution value in milliseconds.