locked
Not able to set Job history setting in SQL Server 2005 Agent RRS feed

  • Question

  • Hi,  I am running SQL Server 2005 Standard with SP 4.

    I set the job history setting to 13 months on the SQL Server Agent and click Okay, everything  seem fine. However when I open the History setting again, the box is unchecked.  Any Idea on why this is happening?  I need to keep the history for SOX compliance.

    Thursday, December 15, 2011 11:07 PM

Answers

  • Hi Mig,

    I can reproduce this issue .There is a sure workaround for this issue and that is to use SP_PURGE_JOBHISTORY.You can setup a job using this.
    http://msdn.microsoft.com/en-us/library/ms175044(v=SQL.90).aspx

    This is known issue in SQL Server.Please refer to this thread for more details.


    Best Regards,
    Peja

    Please remember to click "Mark as Answer" on the post that helps you, and to click "Unmark as Answer" if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.
    • Proposed as answer by Olaf HelperMVP Saturday, December 17, 2011 11:16 AM
    • Marked as answer by Peja Tao Monday, December 26, 2011 3:25 AM
    Friday, December 16, 2011 7:10 AM
  • Hi Miguel,

    Please use the below query and set the oldest_date. The oldest record to retain in the history. oldest_date is datetime, with a default of NULL. When oldest_date is specified, sp_purge_jobhistory only removes records that are older than the value specified.

    sp_purge_jobhistory 
       {   [ @job_name = ] 'job_name' |
         | [ @job_id = ] job_id }
       [ , [ @oldest_date = ] oldest_date ]


    Best Regards,
    Peja

    Please remember to click "Mark as Answer" on the post that helps you, and to click "Unmark as Answer" if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.
    • Marked as answer by Peja Tao Monday, December 26, 2011 3:25 AM
    Wednesday, December 21, 2011 2:04 AM

All replies

  • Hi Mig,

    I can reproduce this issue .There is a sure workaround for this issue and that is to use SP_PURGE_JOBHISTORY.You can setup a job using this.
    http://msdn.microsoft.com/en-us/library/ms175044(v=SQL.90).aspx

    This is known issue in SQL Server.Please refer to this thread for more details.


    Best Regards,
    Peja

    Please remember to click "Mark as Answer" on the post that helps you, and to click "Unmark as Answer" if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.
    • Proposed as answer by Olaf HelperMVP Saturday, December 17, 2011 11:16 AM
    • Marked as answer by Peja Tao Monday, December 26, 2011 3:25 AM
    Friday, December 16, 2011 7:10 AM
  • Hi Peja,

    What I want to do is keep the job history for a year and it was only keeps it for 6 months.  I want to increase the retention.

    Thank You,

    Miguel


    Miguel
    Friday, December 16, 2011 4:19 PM
  • Hi Miguel,

    Please use the below query and set the oldest_date. The oldest record to retain in the history. oldest_date is datetime, with a default of NULL. When oldest_date is specified, sp_purge_jobhistory only removes records that are older than the value specified.

    sp_purge_jobhistory 
       {   [ @job_name = ] 'job_name' |
         | [ @job_id = ] job_id }
       [ , [ @oldest_date = ] oldest_date ]


    Best Regards,
    Peja

    Please remember to click "Mark as Answer" on the post that helps you, and to click "Unmark as Answer" if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.
    • Marked as answer by Peja Tao Monday, December 26, 2011 3:25 AM
    Wednesday, December 21, 2011 2:04 AM