How can I check whether a SQL job was deleted and who?


I am not 100% sure, but i think i had a SQL job in one of my instance. But I can not see it now. it seems that somebody deleted that. I need to get evidence that there was truly a sql job in my instance. is there any system table that hold the information of all the job that was created into the instance even it is deleted. i can only see the current jobs in MSDB database. but i need to see that job there was but not any more.

more ▼

asked Mar 28 '12 at 04:12 PM in Default

Zahid gravatar image

91 9 10 11

(comments are locked)
10|1200 characters needed characters left

3 answers: sort oldest
You can get job_id for any created job from dbo.sysjobs table in MSDB database. In case the job is deleted and created again the job_id will be different.
more ▼

answered Mar 28 '12 at 08:05 PM

inder gravatar image

211 16 19 22

(comments are locked)
10|1200 characters needed characters left

Assuming that if it was deleted that the job history was not removed along with it, you should be able to find traces of it in

select * from msdb.dbo.sysjobhistory


select * from msdb.dbo.sysjobactivity
Can't promise anything, but that might be a place to start.
more ▼

answered Mar 28 '12 at 08:07 PM

JohnM gravatar image

5.9k 1 3 7

John I think job history is vanished when job is deleted.
Mar 28 '12 at 08:23 PM inder

Usually yes, but we don't know how it might have been deleted. You can delete a job and retain the history if specified.


As I mentioned, I assumed that if there is any remains, they might be able to locate traces of it in the tables mentioned.

My guess is that there isn't any history, but you never know. =)

Mar 28 '12 at 09:02 PM JohnM
(comments are locked)
10|1200 characters needed characters left

Another thought, do you currently back up the MSDB database? If so, do you have a valid backup (from the time that you thought that the job was present) that you might be able to restore somewhere to see if the job is present?

Just a thought.

As a side note, if you are not backing it up, you should. ;-)
more ▼

answered Mar 28 '12 at 09:03 PM

JohnM gravatar image

5.9k 1 3 7

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Mar 28 '12 at 04:12 PM

Seen: 2345 times

Last Updated: Mar 28 '12 at 09:04 PM