Learn more about SQL Server tools

mssqltips logo
giveaway
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

SQL Server 2005 Error Log Management


By:   |   Read Comments (1)   |   Related Tips: 1 | 2 | 3 | More > Error Logs

Problem
My SQL Server 2005 error log is getting very large due to auditing login activity and freezes Management Studio when it loads.  On another SQL Server I have lost much of the historical error log data from SQL Server service restarts and Windows reboots.  Do I have any options to address these two needs to review and retain this data in an easy manner?

Solution
Yes - A few options are available to address the size and number of error logs for both SQL Server and SQL Server Agent.  Let's break these down as well as outline another alternative to review these files without locking Management Studio.

SQL Server Error Log

To limit the size of the SQL Server error log, the sp_cycle_errorlog system stored procedure can be issued to start a new error log.  Depending on the growth rate of the SQL Server error log dictates when sp_cycle_errorlog should be issued.  Reference the code below as an example.

EXEC master.sys.sp_cycle_errorlog;

-- Expected successful output
-- DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Next, the easiest means to address this need would be to schedule a SQL Server Job to support the need.  Reference the SQLServer2005_CycletheErrorLog_Job.txt as a point of reference.

Finally, to address not loosing the historical SQL Server error log, the number of logs should be expanded beyond the default of 7.  The maximum number of logs is 99 for the SQL Server error log.  When it comes to expanding the number of SQL Server error logs, follow these steps:

  • Open Management Studio
  • Navigate to root | Management folder | SQL Server Logs folder
  • Right click on the SQL Server Logs folder and select the 'Configure' option
  • Select the 'Limit the number of error log files before they are recycled' check box
    • Reference the screen shot below as a point of reference
  • Enter the desired number of error logs in the 'Maximum number of error log files'
    • Reference the screen shot below as a point of reference   
  • Press the 'OK' button to save the configuration

Alternatively, the following code can be used to automate the process across multiple SQL Servers:

USE [master]
GO
EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'NumErrorLogs', REG_DWORD, 50
GO

SQL Server Agent Error Log

The SQL Server Agent error log follows much of the same paradigm as the SQL Server error log.  The recycling process is completed by the sp_cycle_agent_errorlog in the MSDB database.

EXEC msdb.dbo.sp_cycle_agent_errorlog;
-- Expected successful output
-- Command(s) completed successfully.

In terms of configuring a specified number of SQL Server Agent error logs, this is not possible.  Only the current and 9 additional (a total of 10) SQL Server Agent error logs will be retained.  What can be configured are the type of messages (Errors, Warnings, Informational ) that are written to the SQL Server Agent error logs.  To access this interface, follow these steps:

  • Open Management Studio

  • Navigate to root | SQL Server Agent | Error Logs folder

  • Right click on the Error Logs folder and select the 'Configure' option, reference the screen shot below

To automate this process across servers, reference the sp_set_sqlagent_properties system stored procedure.  Below outlines a sample execution.

USE [msdb]
GO
EXEC msdb.dbo.sp_set_sqlagent_properties @errorlogging_level=7
GO

Alternative Error Log Access

The primary interface to access the SQL Server Error Logs is via the Log File Viewer.  This application provides insight into the SQL Server and Windows logs.  This application provides a means to review multiple logs as well as search and filter the logs.  If you do have a large error log that causes issues for this application, this should be copied and pasted and then the copied log can be reviewed via DOS's type command.  Although this is a less than ideal interface, it appears to provide immediate access and will not affect SQL Server writing new records to the original log.  Below is an example of reading a SQL Server Error log via DOS's type command.

Next Steps



Last Update:






About the author
MSSQLTips author Jeremy Kadlec Since 2002, Jeremy Kadlec has delivered value to the global SQL Server community as an Edgewood Solutions SQL Server Consultant, MSSQLTips.com co-founder and Baltimore SSUG co-leader.

View all my tips





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


SQL tips:

*Enter Code refresh code     



Tuesday, April 23, 2013 - 3:41:01 AM - Rasika Ogale Back To Top

I Execute the query

 

EXEC master.sys.sp_cycle_errorlog;

And i got the error mentioned here as :

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

 
Then I set 'Limit the number of error log files before they are recycled'  to 50.

And refreshed my Server. Still it giving the same error.

Please provide the solution

Learn more about SQL Server tools