Home / server / Questions / 44019
Accepted
Tim Meers
Asked: 2009-07-23 05:37:33 +0800 CST

Verify jobs running on an SQL 2000 database

  • 772

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.

sql-server

3 Answers

  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
  2. Best Answer

    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

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

    • 0