Ever wondered why the SQL Agent job execution history gets deleted at regular intervals?
The default setting is to limit the history to a configurable number of rows. The default setting can be changed in Management Studio by right clicking “SQL Server Agent” and selecting properties. Select the “History” page:
The first group of settings is for configuring the number of rows to keep in the job history table, msdb.dbo.sysjobhistory. The first, “Maximum job history log size (in rows)” sets a maximum number of rows for the whole table. The second setting, “Maximum job history rows per job”, limits the number of rows per job. There settings can also be changed with the (undocumented, use at own risk) system stored procedure sp_set_sqlagent_properties:
EXEC msdb.dbo.sp_set_sqlagent_properties @jobhistory_max_rows=1000, @jobhistory_max_rows_per_job=100
The last setting in this dialog is for “one time use” only. If you use this, records in the job history table older than this value get deleted when you press OK. It is not a setting to automatically delete records older that a specified value. The system stored procedure that is used is sp_purge_jobhistory:
EXEC msdb.dbo.sp_purge_jobhistory @oldest_date='2014-09-25T05:00:00'
I’d strongly recommend that you limit the number of rows in the job history table somehow. Otherwise it will almost be impossible, or at least very slow, to view the log records in SSMS. If you instead want to purge the job history table after an amount of time has passed, use Ola Hallengrens “Maintenance Solution” which will (among a lot of other things) create a SQL Agent job named sp_purge_jobhistory, that (you guessed it) calls sp_purge_jobhistory. The default is to delete job history older than 30 days, but you can change that.
