Post reply

Deleting Job history for particular time period

  • February 8, 2015 at 6:58 am

    #301006

    Hi,

    How to delete the job history for particular runs.

    Suppose if a job run 3 times/day. I want to keep the oldest information and delete the recent 2 runs history.

  • February 8, 2015 at 7:22 pm

    #1776272

    The only way to do this that I know of is to use sp_purge_jobhistory and assign either job name or job id and oldest datetime (you would have to figure how your schedule fits into this requirement and subtract appropriate number of hours).

    Petr

  • February 9, 2015 at 10:02 am

    #1776430

    delete msdb.dbo.sysjobhistory

    where dbo.agent_datetime(run_date, run_time) between <date_time> and <date_time>

  • February 9, 2015 at 10:05 am

    #1776431

    ramana3327 (2/8/2015)

    Hi,

    How to delete the job history for particular runs.

    Suppose if a job run 3 times/day. I want to keep the oldest information and delete the recent 2 runs history.

    For just a single day?

    I have to wonder why.

    You can set the retention within the agent properties (in addition to the options already provided). But why purge a single job run?

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • February 9, 2015 at 10:07 am

    #1776432

    like this, i think?

    with MyCTE

    AS

    (

    with MyCTE

    AS

    (

    select row_number() over(

    partition by Job_ID,run_date,step_id

    order by run_date desc,run_time desc,step_id) As RW,

    * from msdb.dbo.sysjobhistory

    )

    delete FROM MyCTE WHERE RW >1

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • February 10, 2015 at 1:06 pm

    #1776742

    Thank you guys

Viewing 6 posts - 1 through 5 (of 5 total)

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