Sql-server – Verify jobs running on an SQL 2000 database

sql server

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.

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

Related Solutions

Sql-server – Skip log backup if full backup is running

The solution I like to use (since I don't like enabling and disabling jobs as this can leave jobs disabled if there are issues) is to query the sysprocesses table looking for a backup. Look for a query running aginst the db_id = the database you are backing up, and the command of BACKUP DATABASE. Your step would look something like this.

IF EXISTS (SELECT *
           FROM master.dbo.sysprocesses
           WHERE dbid = db_id('YourDatabase')
                 AND cmd LIKE 'BACKUP DATABASE%')
BEGIN
     RAISERROR('The full backup is still running.', 16, 1)
END

For when you need to worry about full backups running while a log backup is running, use something like this.

WHILE EXISTS (SELECT * FROM master.dbo.sysprocesses WHERE dbid = db_id('YourDatabase')
                     AND cmd LIKE 'BACKUP DATABASE%')
BEGIN
     WAITFOR DELAY '00:01:00'
END

Sql-server – How to find when the stored procedure was deleted and who deleted it

You get the administrative trace:

select * from fn_trace_getinfo(NULL)
where property=2
and traceid = 1

The you look into the administrative trace for events of class 47 Object:Deleted Event Class on object types 8727 Stored Procedure:

select * from fn_trace_gettable('....trc', -1)
where EventClass = 47
and ObjectType=8727

The administrative trace is periodically recycled and about 4-5 traces are kept, you should use the name of the oldest trc file still present.

If the procedure is critical then the DBA should had make sure only authorized personel can modify it or drop it. And it should had in place auditing of schema changes. This is not the fault of whoever dropped the procedure, but entirely the DBA fault.

Best Answer

Related Solutions

Sql-server – Skip log backup if full backup is running

Sql-server – How to find when the stored procedure was deleted and who deleted it

Related Topic