Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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.

share|improve this question
add comment

3 Answers 3

up vote 1 down vote accepted

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
share|improve this answer
    
That rocks. I've tucked that away for future use. –  Tim Meers Jul 22 '09 at 16:04
add comment

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

share|improve this answer
    
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 '09 at 15:59
add comment

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

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.