marrowyung
asked on
check out SQL job deleted
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?
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?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Vitor,
we can only do that by that method ?
we can only do that by that method ?
You can try slubek's solution but if don't works then only with an old backup you'll know which jobs were deleted.
ASKER
slubek,
both query seems the same, and it just has the job_id and instance_id, hard to tell which is which, right?
both query seems the same, and it just has the job_id and instance_id, hard to tell which is which, right?
ASKER
Vitor,
yeah, the restore of MSDB is the last sort, but it didn't say who delete that, right?
yeah, the restore of MSDB is the last sort, but it didn't say who delete that, right?
ASKER
or the executing user in the message field is the only place to check who run that and who is deleteing that ?
Unfortunaly won't say who deleted it. Will only prove that the job existed before.
ASKER
ok. OWOWOO. so from your point of view, no way to find it out anyway ?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
"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 ?
Apex Log for example ? you are trying to see what is inside that transcation log of MSDB ?
Yes. If you have Apex log then you can give a try on it.
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
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
ASKER
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_j obhistory "
this one need a @job_id
EXECUTE msdb.dbo.sp_sqlagent_log_j obhistory @job_id = 'D64C99DF-2A19-402C-A404-C 4D768704E8 E';
prompts an error:
Msg 201, Level 16, State 4, Procedure sp_sqlagent_log_jobhistory , Line 0
Procedure or function 'sp_sqlagent_log_jobhistor y' 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 ?
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_j
this one need a @job_id
EXECUTE msdb.dbo.sp_sqlagent_log_j
prompts an error:
Msg 201, Level 16, State 4, Procedure sp_sqlagent_log_jobhistory
Procedure or function 'sp_sqlagent_log_jobhistor
"is going to be very difficult to find out "who" deleted the job."
so can only from the MSDB log file anyway, right ?
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
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)
(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)
ASKER
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.
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.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
"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?
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?
ASKER
Dear both,
please help to answer this is you can:
https://www.experts-exchange.com/questions/28570501/MySQL-trigger-warning-and-error-handling-control.html
please help to answer this is you can:
https://www.experts-exchange.com/questions/28570501/MySQL-trigger-warning-and-error-handling-control.html
Open in new window