Learn more about SQL Server tools

solving sql server problems for millions of dbas and developers since 2006
join MSSQLTips for free SQL Server tips







Learn more about SQL Server tools








Learn more about SQL Server tools


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

MSSQLTips author Kun Lee By:   |   Read Comments (2)   |   Related Tips: 1 | 2 | 3 | More > SQL Server Agent
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.

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.

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
[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10.SQL2008A\MSSQLServer]
"NumErrorLogs"=dword:0000000a

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.


Last Update: 1/14/2011


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.

View all my tips


print tip Print  
Become a paid author




Recommended For You








Learn more about SQL Server tools
Comments and Feedback:
Friday, February 15, 2013 - 9:19:06 AM - Chris F Read The Tip

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.

 


Monday, July 29, 2013 - 2:50:57 AM - Gemma Read The Tip

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



Post a Comment or Question

Keep it clean and stay on the subject or we may delete your comment.
Your email address is not published. Required fields are marked with an asterisk (*)

*Name   *Email Notify for updates

Signup for our newsletter


Comments
*Enter Code refresh code


 

Sponsor Information







Copyright (c) 2006-2014 Edgewood Solutions, LLC All rights reserved
privacy | disclaimer | copyright | advertise | about
authors | contribute | feedback | giveaways | user groups | community | events | first timer?
Some names and products listed are the registered trademarks of their respective owners.