Post reply

capture deleting job details

  • May 13, 2015 at 1:13 am

    #300625

    Hi,

    One of our Application creating run time SQL job to run batch process and application itself deleting this run time SQL job, but when doing deletion it’s not checking whether job is running or not, just doing direct delete. Our challenge is how to capture deleted job details, after incident happen we could see error details only in this log.

    We can run profile to trace but we don’t know when it will trigger and we cannot possible keep active profiler as it will kill server.so we can't run the trace for log time as we don't know when the issue happens.

    Any suggestions Please.

    Thanks

  • May 13, 2015 at 10:27 am

    #1797609

    Hi DB Experts,

    Any sugessions here please .

    Thanks in advance.

  • May 13, 2015 at 10:41 pm

    #1797747

    From my point of view, since there are not any DDL triggers that handle job and job history and you cannot add triggers to system tables, you are really only left with some bad options other than tracing.

    Ultimately, I would think you would need to set up a trace - and filter it to only handle requests to the MSDB database. Then you use could the extended event filter by batch_text in sql_batch_completed or by statement in rpc_completed. Put the value 'job' (without quotes) in the like_i_sql_unicode_string , which then filter the statement for only tables containing the word Job. This filtering is done at a lower level within process and should have very low impact, and the size of the trace should not be so great that you kill your server. It might take a few tweaks to get just what you need (you may want to go with JobHistory instead if you are only interested in the history). You may also want to consider writing the trace to a table too, and kicking the trace off via a script, as this will keep the interface from consuming resources. Keep in mind that if you start a trace that way, you have to all of the filtering in the tracesetup, which can be time consuming at best.

    I am still not sure you are going to catch what you need from the traces, but at least this should answer what jobs are being created.

    Tim Blum
    Senior Manager, Outsourced Data Services
    Learn2Live at Ureach.com

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply