Contents
How do I change my job history retention in SQL?
In Object Explorer, connect to an instance of the SQL Server Database Engine, and then expand that instance. Right-click SQL Server Agent, and then select Properties. Select the History page, and then select Remove agent history. Select the appropriate number of Days, Weeks, or Months.
What is Sp_purge_jobhistory?
By using sp_purge_jobhistory you can remove history data older than specific date. You just need to pass the @Date as a parameter by using “sp_purge_jobhistory” provided by SQL Server.
How far back does SQL job history go?
Since we can only keep 10 history rows for any single job, the earlier execution gets truncated. SQL Server holds onto step 0 but deletes the history for steps 1 and 2. There is an also issue if you only want to keep an 8- or 12-hour rolling window of job history.
How do I view job history in SQL query?
To view the job history log
- In Object Explorer, connect to an instance of the SQL Server Database Engine, and then expand that instance.
- Expand SQL Server Agent, and then expand Jobs.
- Right-click a job, and then click View History.
- In the Log File Viewer, view the job history.
- To update the job history, click Refresh.
How do I clear the job history log?
To clear the job history log In Object Explorer, connect to an instance of Database Engine. On the Standard bar, click New Query. Copy and paste the following example into the query window and click Execute.
Is there a limit to the size of the job history log?
Right-click SQL Server Agent, and then select Properties. Select the History page, and then confirm that Limit size of job history log is checked. In the Maximum job history log size box, enter the maximum number of rows the job history log should allow.
How to resize the job history log in SQL Server?
To resize the job history log based on time: In Object Explorer, connect to an instance of the SQL Server Database Engine, and then expand that instance. Right-click SQL Server Agent, and then click Properties. Select the History page, and then click Automatically remove agent history.
How to delete job history in SQL Server Agent?
To clear the job history log. Expand SQL Server Agent, and then expand Jobs. Right-click a job and click View history. In the Log File Viewer, select the job for which you want to clear history, and then do one of the following: Click Delete, and then click Delete all history in the Delete History dialog.