1

I have a stored procedure that is being executed by an unknown job. So I'm trying to figure out where the data is stored in SQL 2000 that shows which jobs belong to which database. The reason is that I have a job running that the job name does not have the database name in it. All of the jobs should be something like 'Job name - dbName'.

But for some reason I'm either A: not able to see it frmo Enterprise Manager, or B: the sproc is being executed by another job. Though when I search all the procedures for that procedure name in source control I only find it's name in that one sproc.

I've looked in the msdb.sysjobs table and that gives me jon names, job id's, and other info, but not the database name that it is executing against. I'm just strugling to figure this out.

3 Answers 3

1

This query will give you all of the instances of your proc in your jobs and which database the step that calls the proc is running against, with one caveat: it won't tell you if there is a job that is executing an SSIS package which calls your proc.

SELECT 
    jobs.NAME AS JobName,
    steps.step_id AS StepID,
    steps.step_name AS StepName,
    steps.database_name AS DatabaseForJobStep
FROM msdb..sysjobsteps steps
JOIN msdb..sysjobs jobs ON steps.job_id=jobs.job_id
WHERE steps.command LIKE '%p_mystoredprocname%'
ORDER BY jobs.NAME,Steps.step_id
1
  • That rocks. I've tucked that away for future use.
    – Tim Meers
    Jul 22, 2009 at 16:04
1

The database information is stored in sysjobsteps, not sysjobs. You will also want to check the command column in sysjobsteps to look for code like:

exec mydatabase.dbo.some_procedure

1
  • AHHH The database_name field. I guess I read over that one. I eneded up finding it just before reading this comment. I traced it back using the owner_sid to the master.sysxlogins table. I queried against 2 different sid's (the db specfic, and "mine"). I just looked thru that list for those two and suprisingly found it. But now I know an easier way, sysjobsteps.database_name. Thanks.
    – Tim Meers
    Jul 22, 2009 at 15:59
0

You could put a trace on the DB with SQL Profiler, filter based on the sproc name, and look in the DB column.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .