August 6, 2012 at 6:56 am
Is there a way to find out what jobs are using a certain stored procedure?
August 6, 2012 at 7:12 am
-- What SQL Statements Are Currently Running?
SELECT [Spid] = session_Id
, ecid
, sp.blocked as 'Blocked By'
, [Database] = DB_NAME(sp.dbid)
, nt_domain
, [User] = nt_username
, sp.loginame
, sp.cmd as 'Command'
, [Status] = er.status
, [Wait] = wait_type
, wait_resource
, [Individual Query] = SUBSTRING (qt.text, er.statement_start_offset/2,
(CASE WHEN er.statement_end_offset = -1
THEN (LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2) + 2 -- added + 2
ELSE er.statement_end_offset END - er.statement_start_offset)/2)
,[Parent Query] = qt.text
, Program = program_name
, Hostname
, start_time
, Last_batch
FROM sys.dm_exec_requests er
INNER JOIN sys.sysprocesses sp ON er.session_id = sp.spid
CROSS APPLY sys.dm_exec_sql_text(er.sql_handle) as qt
WHERE session_Id > 50-- Ignore system spids.
AND session_Id NOT IN (@@SPID)-- Ignore this current statement.
ORDER BY 1, 2
August 6, 2012 at 7:44 am
guerillaunit (8/6/2012)
Is there a way to find out what jobs are using a certain stored procedure?
Are you wanting to see only SQL Agent Jobs or as damodharan has already provided, just the currently executing statements across the server?
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
August 6, 2012 at 7:47 am
Here's something I just threw together. Someone else may have a better way, but this is just off the top of my head:
DECLARE @spName VARCHAR(250);
SET @spName = 'sp_syspolicy_purge_history';
SELECT sj.name, sjs.step_id, sjs.step_name, sjs.subsystem, sjs.command
FROM msdb..sysjobsteps sjs
INNER JOIN msdb..sysjobs sj
ON sjs.job_id = sj.job_id
WHERE sjs.command LIKE '%' + @spName + '%'
Obviously, replace sp_syspolicy_purge_history with whatever the sp you are looking for is named.
Jared
CE - Microsoft
August 6, 2012 at 7:53 am
Right click on the stored procedure in SSMS and choose View dependencies.
August 6, 2012 at 8:01 am
I believe the job history information in MSDB is only updated upon completion of the job.
To see jobs that are currently executing, you need to execute the sp_help_job system stored procedure and look at the current execution status:
EXEC msdb.dbo.sp_help_job
1 Executing.
2 Waiting for thread.
3 Between retries.
4 Idle.
5 Suspended.
7 Performing completion actions.
(EDIT - Wow, nevermind... I somehow completely misread the original question!!!)
August 6, 2012 at 9:54 am
Why not something like this?
SELECT
'Job Name' = j.name
'Step Name' = s.step_name
FROM
sysjobs j
INNER JOIN sysjobsteps s ON j.job_id = s.job_id
WHERE
s.command like '%SP_SOME_STORED_PROCEDURE%'
August 6, 2012 at 9:55 am
Ah never mind. Somehow I browsed past Jared's without seeing it. His is better, paramaterized.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply