Contents
How many job Logs does SQL Server keep?
The job step should run the following command. One thing to note is that the default setting for SQL Server only keeps the last 7 logs (active plus 6 archives) as shown below. This may be OK for some to only keep the last 7 logs, but for most cases that may not be enough.
How to customize job log in SQL Server?
To make this customization you just need to add another step to a job as follows. There you go. You now have a job to keep only the last 15 days of the job log history and only the last 3 days for job “Pay Roll Over”. If you need to do this for other jobs, you can just add additional steps.
How to change job history in SQL Server?
You can get to this screen by right clicking on SQL Server Agent and select Properties. Or if you prefer, you can use T-SQL script below to change these properties shown in the screenshot above. Now, create a new SQL job to run once a day and put in this T-SQL code. In this example I will only keep the job history for the last 15 days.
Where does SQL Server Agent store job history?
Still the issue. Please advice. SQL Server Agent stores the history of job execution in system tables in msdb database. The following query gives us the details of last/latest execution of the SQL Server Agent Job and also the next time when the job is going to run (if it is scheduled).
How big should my SQL Server Agent log be?
Figure 1- SQL Server Agent properties. Figure 2- SQL Server Agent history size. We recommend changing the Maximum job history log size (in rows) to 50,000 and Maximum job history rows per job to 1,000. Note: These numbers can vary according to you with your business needs.
How to create procedure logging in SQL Server?
In order to see how long things are taking, and how many rows the previous action modified, I add the current date + time and the last row count to every entry. I use this procedure: CREATE PROCEDURE dbo.