Managing SQL Server Agent Job History Log and SQL Server Error Log

By:   |   Comments (3)   |   Related: 1 | 2 | 3 | > SQL Server Agent


When you install SQL Server, it has a few default configuration settings for SQL Server to manage logs. In some instances, where servers generate too much log info and you need to look at the log it takes a long time to pull up. Also there maybe cases where you want to keep more log info for some jobs versus others. For example, I have a job that runs every minute and it generates a lot of log history, but I only care about the data for the last 3 days. For another job that runs daily, I want to keep the log info for 15 days. I started with a process to pull all history and store in a separate user table, which is still very useful, but for day to day operations I was looking for an easier way to manage this for all of my SQL Server 2005 and later servers.

There are various logs that exist on SQL Server. For this tip I am going to cover the below topics and briefly talk about the syspolicy_purge_history job on SQL 2008.
  • SQL Server Logs
  • SQL Job History

Purging SQL Server Job History

Jeremy Kadlec wrote a article Retaining SQL Server Job History and it explains how job history is stored which you may want to read first. What I am going to do is to customize the options and control the SQL Job history. Note that this only works for SQL Server 2005 and later.

First, you will need to disable all the options as shown below. You can get to this screen by right clicking on SQL Server Agent and select Properties.

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.

EXEC msdb.dbo.sp_set_sqlagent_properties 

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.

DECLARE @OldestDate datetime
SET @OldestDate = GETDATE()-15
EXEC msdb.dbo.sp_purge_jobhistory @oldest_date=@OldestDate@

Now, assume I have a job called "Pay Roll Over" and it runs every 10 minutes and I only need to keep the last 3 days of the job log for troubleshooting purposes. To make this customization you just need to add another step to a job as follows.

DECLARE @OldestDate datetime
DECLARE @JobName varchar(256)
-- Keep Last 3 days
SET @OldestDate = GETDATE()-3
SET @JobName = 'Pay Roll Over'
EXEC msdb.dbo.sp_purge_jobhistory 

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.

Cycle SQL Server Logs

I have a similar situation with the SQL Server Logs where the logs get really large and it takes along time to open the log to find errors. To handle this, you can use sp_cycle_errorlog to close the active error log and create a new error log. This works for older versions of SQL server like SQL 2000 as well.

You can create a new job to run each day or on whatever schedule you prefer. The job step should run the following command.

exec master.dbo.sp_cycle_errorlog

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.

sql server only keeps the last 7 logs

This may be OK for some to only keep the last 7 logs, but for most cases that may not be enough. If you need to keep more than 7 logs, you can read this article "How to increase the number of SQL Server error logs".

This can also be done by updating the registry which is usually what I do by running the below script to apply the change. This will increase the value to 10 archive logs.

Windows Registry Editor Version 5.00

This is how it looks if you use RegEdit.

updating the registry to increase the value to 10

and it will let you keep 10 archive copies as shown below.

1 active log and 9 archive logs

Policy Purge History job (syspolicy_purge_history)

The job syspolicy_purge_history is part of the default installation and you want to make sure this job doesn't fail. It is a known issue that for clusters this job may fail and I just wanted to point this out. You can read this article "Why SYSPOLICY_PURGE_HISTORY job fails in SQL Server 2008 Failover Cluster Instance" for more information.

The issue is caused by the name of the server. Assume your physical SQL Server name is "MYSERVERNode1" and your Virtual SQL Server Instance name is "MYSQLSERVER\SQL2008A":

This is the before value in this job that causes the issue

(Get-Item SQLSERVER:\SQLPolicy\MYSERVERNode1\SQL2008A).EraseSystemHealthPhantomRecords()

and this is what it should be

(Get-Item SQLSERVER:\SQLPolicy\MYSQLSERVER\SQL2008A).EraseSystemHealthPhantomRecords()

This change will address the issue without recreating the job, at least this is the case for all the clusters that I have worked with.

Sample Code to Create a Job

Here is the code that I used to create the job for this sample. You can customize as needed. You should remove the 2nd step from the script as it is customized to purge a specific job that I mentioned above in the example.

Next Steps
  • If you have general scripts that you run after SQL Server installation, this is another good script to run after installation is done to keep your servers consistent.

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

About the author
MSSQLTips author Kun Lee Kun Lee is a database administrator and his areas of interest are database administration, architecture, data modeling and development.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Comments For This Article

Friday, September 19, 2014 - 1:48:02 PM - Paul Back To Top (34630)

Why did MS go backwards with the editor in the SQL Agent ?  In SQL 2000 it was much like Query Analyzer, meaning keywords and commands were color coded, after 2000 pretty much just Notepad, no color coding AND why does the editor not recognize agent Tokens ?  really frustrating that I need to comment out code using tokens prior to parsing, as almost intentional and they want us to use VS for everything.  I won't even go into DTS vs SSIS and that debacle.

Monday, July 29, 2013 - 2:50:57 AM - Gemma Back To Top (26039)

Why is it it takes more time to INSERT with BEGIN/COMMIT TRANS than running  a complicated SELECT statement?

Friday, February 15, 2013 - 9:19:06 AM - Chris F Back To Top (22153)

Hi, I'm hoping someone can help me with this.

I'm trying to view job history in SQL Server Management Studio.  I used to be able to right-click on a job, choose "View History", and it would display the history for ONLY that job.  But my system recently had some Windows updates applied, and now when I right-click a job and choose "View History", it displays the history for ALL jobs.  I cannot find an option anywhere to set it back to only show history for the one job I clicked on.

Thank you; this is maddening.


get free sql tips
agree to terms