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.
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.
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'
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.
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 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.
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.
Last Update: 1/14/2011
About the author
Kun Lee is a database administrator and his areas of interest are database administration, architecture, data modeling and development.
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.
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.