Problem
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.
Solution
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.
EXEC msdb.dbo.sp_set_sqlagent_properties
@jobhistory_max_rows=-1,
@jobhistory_max_rows_per_job=-1
GO
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
@job_name=@JobName,
@oldest_date=@OldestDate
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.
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
[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10.SQL2008A\MSSQLServer]
“NumErrorLogs”=dword:0000000a
This is how it looks if you use RegEdit.
and it will let you keep 10 archive copies as shown below.
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.
