Solved

check out SQL job deleted

Posted on 2014-11-27
20
685 Views
Last Modified: 2014-12-17
dear all,

right now we suspected that some SQL jobs has been deleted without our inspection, anyway to check what job has been delete during the last 3 months?
0
Comment
Question by:marrowyung
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 10
  • 5
  • 4
  • +1
20 Comments
 
LVL 7

Assisted Solution

by:slubek
slubek earned 166 total points
ID: 40470077
Have you tried
select * from msdb.dbo.sysjobhistory

Open in new window

or
select * from msdb.dbo.sysjobactivity

Open in new window

?
1
 
LVL 50

Expert Comment

by:Vitor Montalvão
ID: 40470149
You need to have an old backup of MSDB database, then restore it with another name and check in sysjobs table if the jobs existed in that time are the same that you has now:
SELECT *
FROM sysjobs
ORDER BY name

Open in new window

0
 
LVL 1

Author Comment

by:marrowyung
ID: 40472298
Vitor,

we can only do that by that method ?
0
Comparison of Amazon Drive, Google Drive, OneDrive

What is Best for Backup: Amazon Drive, Google Drive or MS OneDrive? In this free whitepaper we look at their performance, pricing, and platform availability to help you decide which cloud drive is right for your situation. Download and read the results of our testing for free!

 
LVL 50

Expert Comment

by:Vitor Montalvão
ID: 40472302
You can try slubek's solution but if don't works then only with an old backup you'll know which jobs were deleted.
0
 
LVL 1

Author Comment

by:marrowyung
ID: 40473054
slubek,

both query seems the same, and it just has the job_id and instance_id, hard to tell which is which, right?
0
 
LVL 1

Author Comment

by:marrowyung
ID: 40473058
Vitor,

yeah, the restore of MSDB is the last sort, but it didn't say who delete that, right?
0
 
LVL 1

Author Comment

by:marrowyung
ID: 40473069
or the executing user in the message field is the only place to check who run that and who is deleteing that ?
0
 
LVL 50

Expert Comment

by:Vitor Montalvão
ID: 40473210
Unfortunaly won't say who deleted it. Will only prove that the job existed before.
0
 
LVL 1

Author Comment

by:marrowyung
ID: 40473220
ok. OWOWOO. so from your point of view, no way to find it out anyway ?
0
 
LVL 50

Accepted Solution

by:
Vitor Montalvão earned 167 total points
ID: 40473222
You could have a chance to know it but then msdb need to be in full recovery model and you need to have the transaction log backups from that database. With that you'll need a log reader (usually a 3rd party software) that will be able to read a transaction log and then you'll found who and when deleted the job.
0
 
LVL 1

Author Comment

by:marrowyung
ID: 40473225
"With that you'll need a log reader (usually a 3rd party software) that will be able to read a transaction log and then you'll found who and when deleted the job. "

Apex Log for example ? you are trying to see what is inside that transcation log of MSDB ?
0
 
LVL 50

Expert Comment

by:Vitor Montalvão
ID: 40473233
Yes. If you have Apex log then you can give a try on it.
0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 40473340
Ouch...

By default MSDB uses simple recovery. So, unless you can interrogate MSDB itself, then it is going to be very difficult to find out "who" deleted the job.

Have you changed MSDB log to be full recovery ?

You might be able to find out the last time a job was executed, and then try to pinpoint activity during that time frame to see who was active at the time, but I think you will be out of luck.

Sorry about the bad news...

I will do a few searches on information and books that I have access to and see if there is any other glimmer of hope.

One thought to help pinpoint is querying the sysjobsteps
0
 
LVL 1

Author Comment

by:marrowyung
ID: 40473364
Mark,

good to see you here again !

"
 Have you changed MSDB log to be full recovery ?"
no. so this mean it is a must ?


one question, anyone know if Apex Log tools can open the MSDB log when MS SQL is operating?

"EXECUTE msdb.dbo.sp_sqlagent_log_jobhistory "

this one need a @job_id

EXECUTE msdb.dbo.sp_sqlagent_log_jobhistory @job_id = 'D64C99DF-2A19-402C-A404-C4D768704E8E';

prompts an error:

Msg 201, Level 16, State 4, Procedure sp_sqlagent_log_jobhistory, Line 0
Procedure or function 'sp_sqlagent_log_jobhistory' expects parameter '@step_id', which was not supplied.

"is going to be very difficult to find out "who" deleted the job."

so can only from the  MSDB log file anyway, right ?
0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 40473366
Have  a read of : http://blogs.msdn.com/b/poojakamath/archive/2014/02/18/the-job-history-of-all-sql-server-scheduled-jobs-are-getting-deleted-automatically-on-a-daily-basis.aspx  (bit after the event, but could help if a recurring problem), also might be worth searching for : sp_delete_job
0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 40473369
If you are trying to find out who deleted a job by inspecting the log files, then yes, the log file would need to be as a result of having Full Recovery mode set on your MSDB (which is auto reset to simple everytime you run setup).

(BTW: I was in the middle of editing my post as you were reading it. Should have been querying sysjob... as per what can now be seen in my previous entry don't worry about that execute unless you know all the params)
0
 
LVL 1

Author Comment

by:marrowyung
ID: 40475221
Mark,

you mean you write this post:

http://blogs.msdn.com/b/poojakamath/archive/2014/02/18/the-job-history-of-all-sql-server-scheduled-jobs-are-getting-deleted-automatically-on-a-daily-basis.aspx 

?

so as long as my MSDB is not in full recovery mode then nothing can be done. so this is it.
0
 
LVL 51

Assisted Solution

by:Mark Wills
Mark Wills earned 167 total points
ID: 40475283
No, I meant the earlier post. I realised that you need a LOT of detail to use that stored procedure to get history. Not that it would tell you who deleted, but would give you a timeline - but then (as others have said) there are system views/tables that can help identify a timeline, so changed my post from using the stored procedure to using the sysjob* views.

Sorry about that confusion.

To see deleted jobs, then you need the log file. For that log file to be useful, the DB needs to have been in full recovery mode.

Even then, you would need the timeline and tools to review the log file. Even then, the log may very well show a generic user or a process such as evidenced in the above link.

So, my initial response of "Ouch" is very much the case. I strongly believe (having been there before) that nothing can be done.

 Sometimes "it can't be done" is the correct answer, as much as we may wish it to be otherwise.

Moving forward, because jobs are removed using the stored procedure SP_DELETE_JOB you can create a SERVER audit to monitor that : http://blogs.msdn.com/b/sqlagent/archive/2011/02/21/auditing-sql-agent-job-creation-and-deletion.aspx

Or, if scheduling user generated tsql procedures, you can track modifications to those procedures using DDL triggers : http://technet.microsoft.com/en-us/library/ms186406.aspx  (I think we have discussed DDL triggers before)
0
 
LVL 1

Author Comment

by:marrowyung
ID: 40475342
"Sometimes "it can't be done" is the correct answer, as much as we may wish it to be otherwise."

MS DBA exam like this !

"Or, if scheduling user generated tsql procedures, you can track modifications to those procedures using DDL triggers : http://technet.microsoft.com/en-us/library/ms186406.aspx  (I think we have discussed DDL triggers before) "

yes, but if someone use a system account to create that procedure, we can't do anything anyway, right?
0
 
LVL 1

Author Comment

by:marrowyung
ID: 40475422
Dear both,

please help to answer this is you can:

http://www.experts-exchange.com/Database/MySQL/Q_28570501.html
0

Featured Post

SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

734 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question