locked
Job History Retention RRS feed

  • Question

  • Hello All,

    I have 34 jobs on my sql server production environment. Some of them run almost once every 10 mins. The issue is I am only able to hold only one entry in the jobhistory for any job (whether the schedule is a day, month, minute). How can I increase this to hold a history of upto one week.

    The current history settings in the agent are

    Maximum job history log size(in rows): 1000

    Maximum job history rows per job: 100

    Thanks for you help.

    Friday, November 30, 2012 7:02 PM

Answers

  • --Set new Limit size of job history
    EXEC msdb.dbo.sp_set_sqlagent_properties @jobhistory_max_rows=-1,@jobhistory_max_rows_per_job=-1
    
    --delete jobhistory older than 7 days and schedule following as a job or use gui
    DECLARE @oldest_date DATETIME
    SET @oldest_date = DATEADD(DAY,-7,GETDATE())
    PRINT @oldest_date
    EXEC msdb..sp_purge_jobhistory NULL,NULL,@oldest_date

    Jon

    • Proposed as answer by LearnerSql Saturday, December 1, 2012 12:34 PM
    • Marked as answer by Olaf HelperMVP Friday, June 27, 2014 5:24 PM
    Saturday, December 1, 2012 12:50 AM

All replies

  •  rightclick on SQL Server Agent, properties-> History, You can increase the history log size(in rows) and max Job history rows per job . 
    • Edited by SQLmaddy Friday, November 30, 2012 7:48 PM enhancement
    Friday, November 30, 2012 7:47 PM
  • Thanks for the reply SQLMaddy. I know that we can change the value of max Job history rows per job. But I only want history for 1 week and not more than that. So is there a specific number that I need to increase this value to?

    Thanks

    Friday, November 30, 2012 7:57 PM
  • You can't.  The SQL Agent job history isn't based on date, it keeps X rows in the log file for ALL jobs.

    Your only option is to increase the max job history from 1000, to something larger for your retention.

    • Proposed as answer by Rama Udaya Friday, November 30, 2012 9:47 PM
    • Unproposed as answer by Rama Udaya Saturday, December 1, 2012 3:57 AM
    Friday, November 30, 2012 9:39 PM
  • I have already done that and was only looking if such an option exists. Thanks
    Friday, November 30, 2012 10:04 PM
  • I dont think that you can do in the GUI as you can see that is the built -in that exists.. not sure if you can do it the in the system catalog level so it also depends like same as GUI method

    Thanks, Rama Udaya.K (http://rama38udaya.wordpress.com) ---------------------------------------- Please remember to mark the replies as answers if they help and UN-mark them if they provide no help,Vote if they gives you information.

    Friday, November 30, 2012 11:10 PM
  • --Set new Limit size of job history
    EXEC msdb.dbo.sp_set_sqlagent_properties @jobhistory_max_rows=-1,@jobhistory_max_rows_per_job=-1
    
    --delete jobhistory older than 7 days and schedule following as a job or use gui
    DECLARE @oldest_date DATETIME
    SET @oldest_date = DATEADD(DAY,-7,GETDATE())
    PRINT @oldest_date
    EXEC msdb..sp_purge_jobhistory NULL,NULL,@oldest_date

    Jon

    • Proposed as answer by LearnerSql Saturday, December 1, 2012 12:34 PM
    • Marked as answer by Olaf HelperMVP Friday, June 27, 2014 5:24 PM
    Saturday, December 1, 2012 12:50 AM
  • Assume all Jobs Run every 10 minutes. 

    There are 10080 minutes in a week. Number of times a job will run in a week:

    10080/10 = 1008

    This is also the number of rows you will need to store in the Job History Per Job, if you want to keep everything for the week but as others have suggested I would adjust this upwards for comfort level. So let's say 1050 per job.

    You say you have 34 jobs therefore the the history settings in order to accomodate your weeks worth of monitoring agent history should be at least:

    34 * 1050 = 35700

    John Sansom | SQL Server DBA Blog | @SQLBrit on Twitter

    Saturday, December 1, 2012 8:07 AM
  • In addition, to setting either the Max Job History or the Max Job History Per Job to higher numbers you also have the ability to turn these off completely and not have SQL Server enforce any limitation. This may sound like a good thing, but if you have a job that runs every minute this will consume a lot of space for all of these job runs, so you need to be aware of how often your jobs are running and also how much job history you really want to keep.  Also, with no limitations the history table will grow forever.

    A couple of other options on this screen are with SQL Server 2000 you have the ability to clear the entire job log from this screen, so you can clear out all historical run data.  With SQL Server 2005 you now have the ability to set a value for how old you want to keep historical data.  This will allow you to trim and manage your SQL Server Agent history log.

    http://www.mssqltips.com/sqlservertip/1269/missing-sql-server-agent-history/


    Manish

    Saturday, December 1, 2012 12:48 PM
  • You can't.  The SQL Agent job history isn't based on date, it keeps X rows in the log file for ALL jobs.

    Your only option is to increase the max job history from 1000, to something larger for your retention.

    I increased the value the max job history rows per job from 100 to 1000 and I still see no history for few jobs. Should I increase to a different value?  If I increase it to a value above 1000 I get the below error.

    "The specified '@jobhistory_max_rows' is invalid (valid values are: 1..1000)."

    • Edited by oleolehoohoo Monday, December 3, 2012 7:47 PM Incomplete
    Monday, December 3, 2012 7:45 PM
  • They are after all just rows, and you can create and run a new job (!) that runs once a day or so and copies out all the new rows to your own archive, as selectively as you want, and then deletes them from the system table.

    Your archive won't show in the GUI, but the contents will all still be there for your own queries.

    Josh

    • Marked as answer by Maggie Luo Thursday, December 6, 2012 10:22 AM
    • Unmarked as answer by oleolehoohoo Thursday, December 6, 2012 6:37 PM
    Monday, December 3, 2012 10:58 PM
  • Is it possible to script that stores the history of specific jobs in a separate table for 7 days? Because there are few specific jobs that dont have a history even though they ran successful. The sysjobshistory table returns nothing even though the jobs ran fine.
    • Edited by oleolehoohoo Thursday, December 6, 2012 4:28 PM issue not resolved
    Thursday, December 6, 2012 4:25 PM
  • As part of a maintenance plan, just add a general task "History Cleanup Task" and define the Backup and restore history, SQL Server Agent job History, and Maintenance plan History as desired.  Then set the 'Remove history data older than' to the desired time/date.
    • Proposed as answer by BeaconMRat Thursday, January 9, 2014 2:21 PM
    Tuesday, January 7, 2014 7:57 PM
  • That does not affect the OP of the job history for Agent.  If "Limit the size of job history log" is check SQL Agent history is controlled by:

    Maximum job history log size(in rows): 1000 default

    Maximum job history rows per job: 100 default

    Once you hit those limits, the history gets deleted.

    http://technet.microsoft.com/en-us/library/ms190956(v=sql.110).aspx

    Tuesday, January 7, 2014 8:23 PM
  • what about job step ?
    if there are 5 steps inside a single job, what's the maximum ?
    Thursday, June 12, 2014 7:47 AM
  • I have disable the "Limit size of job history log" by query because In my case due to some problem SQL Server agent properties is not opening and giving error.So I just want to check that is this setting really disable or not.How can i check this:

    TITLE: Microsoft SQL Server Management Studio
    ------------------------------

    Cannot show requested dialog.

    ------------------------------
    ADDITIONAL INFORMATION:

    Property ErrorLogFile is not available for JobServer '[CLST-CAN01SQL02]'. This property may not exist for this object, or may not be retrievable due to insufficient access rights.  (Microsoft.SqlServer.Smo)

    For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=11.0.3368.0+((SQL11_SP1_QFE-CU).130522-1657+)&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.PropertyCannotBeRetrievedExceptionText&EvtID=ErrorLogFile&LinkId=20476

    ------------------------------
    BUTTONS:

    OK
    ------------------------------



    Thanks

    Thursday, June 30, 2016 10:31 PM