Tuesday, July 1, 2014

SQL Agent History Increase Number of Days in History

Per http://dba.stackexchange.com/questions/44293/history-from-sql-agent-jobs-sql-server-2005

Examples:

EXEC msdb.dbo.sp_set_sqlagent_properties
    @jobhistory_max_rows = 1000,
    @jobhistory_max_rows_per_job = 100


--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

No comments:

Post a Comment