Best Practice – SQL Server Error Log Management

Leave a comment (1) Go to comments

What is the SQL Server Error Log?

SQL Server maintains its own error logs that contain messages describing informational and error events. The SQL Server error log is a great place to find information about what is happening on your database server. Each SQL Server Error log will have all the information related to failures / errors that has occurred since SQL Server was last restarted or since the last time you have recycled the error logs.

By default, there are six achieved SQL Server Error Logs along with the ERRORLOG which is currently used. However, it is a Best Practice to increase the number of SQL Server Error Logs from the default value of six. In this tip, you will see the steps which you need to follow to increase the number of SQL Server Error Logs.

A new SQL Server error log file will be created when one of two things happens:

  1. The SQL Server service is started
  2. sp_cycle_errorlog is called

Best Practice – SQL Server Error Log

  1. Ensure you Error log directory is backed up regularly, with windows OS backup
  2. Increase the number of SQL Server Error Logs from the default value of six.
  3. Make a schedule job to Recycle the Error on a specified schedule to ensure, you log size in control as large files takes time to read data
  4. Check SQL Server Error log on daily basis as all important / critical messages and warnings are logged in SQL Server Error Log

How to specify number of log files to be maintained by SQL Server


SQL Server Management Studio -> Management -> SQL Server Logs
Right click -> Configure

Best Practice   SQL Server Error Log Management stop sql server error log stop sql server agent log start new sql server error log start new sql server agent log sql server sp cycle errorlog SQL Server Error Log Best Practices sp cycle errorlog Recycle SQL Server Error Log graphically Recycle SQL Server Error Log Recycle SQL Server Agent Log grphically Recycle SQL Server Agent Log Increase the Number of SQL Server Error Logs How to increase the number of SQL Server error logs Changing Number of SQL Server error logs in SQL 2008 Changing Number of SQL Server error logs in SQL 2005 Best Practice   SQL Server Error Logs

Best Practice   SQL Server Error Log Management stop sql server error log stop sql server agent log start new sql server error log start new sql server agent log sql server sp cycle errorlog SQL Server Error Log Best Practices sp cycle errorlog Recycle SQL Server Error Log graphically Recycle SQL Server Error Log Recycle SQL Server Agent Log grphically Recycle SQL Server Agent Log Increase the Number of SQL Server Error Logs How to increase the number of SQL Server error logs Changing Number of SQL Server error logs in SQL 2008 Changing Number of SQL Server error logs in SQL 2005 Best Practice   SQL Server Error Logs

Same way, we can configure the number of SQL Server Agent Logs too.

SQL Server Management Studio -> SQL Server Agent-> Server Logs
Right click -> Configure

How to Start a New SQL Server Error Log

sp_cycle_errorlog, stored procedure is used to recycle the SQL Server error log

How to Start a New SQL Server Agent Log

USE MSDB
GO
EXEC dbo.sp_cycle_agent_errorlog
GO

OR, Agent Log can be cycle graphically also

Best Practice   SQL Server Error Log Management stop sql server error log stop sql server agent log start new sql server error log start new sql server agent log sql server sp cycle errorlog SQL Server Error Log Best Practices sp cycle errorlog Recycle SQL Server Error Log graphically Recycle SQL Server Error Log Recycle SQL Server Agent Log grphically Recycle SQL Server Agent Log Increase the Number of SQL Server Error Logs How to increase the number of SQL Server error logs Changing Number of SQL Server error logs in SQL 2008 Changing Number of SQL Server error logs in SQL 2005 Best Practice   SQL Server Error Logs

Notes

  1. By default, SQL Server maintains a minimum of 6 Error Log Files.
  2. Each time the SQL Server is restarted, the Current Active Log File is recycled and new one created.
  3. The Error Log Files are stored in the “Microsoft SQL Server\MSSQL.1\MSSQL\LOG\” Directory.
  4. It stores, security related login information, Logins info such as Failure Logins or Failure and Success Logins
  5. Error Logs also stores, changes in Database Settings, Database backup related information; both successful backups and failures are reported.
  6. If case SQL Services failed to start, SQL Server Error Log is the first thing, which shuld be looked at to figure out the reason, why its failing.
  7. sp_cycle_errorlog, can only be executed by members of sysadmins

Tags : How to increase the number of SQL Server error logs,Increase the Number of SQL Server Error Logs,Best Practice – SQL Server Error Logs,Changing Number of SQL Server error logs in SQL 2008,Changing Number of SQL Server error logs in SQL 2005,sp_cycle_errorlog , sql server sp_cycle_errorlog , SQL Server Error Log Best Practices,Recycle SQL Server Error Log,Recycle SQL Server Agent Log,Recycle SQL Server Error Log graphically,Recycle SQL Server Agent Log grphically, start new sql server agent log,start new sql server error log,stop sql server agent log,stop sql server error log,

EOF - Best Practice – SQL Server Error Log Management, SQL Server 2012 , 2008 R2 , 2008 , 2005 , 2000
Leave a comment

1 Comments.

  1. I would like to know where the info holding the value 99 is into the Windows Registries .

    Regards,
    Mark

Leave a Reply

Your email address will not be published. Required fields are marked *


*


You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

Notify me of followup comments via e-mail. You can also subscribe without commenting.