How to manage the SQL Server error log

Summary

The Microsoft SQL Server error log contains lots of information that is generated by the SQL Server. The error log contains informational messages, warnings, and information about critical events. The error log also contains information about user-generated messages and auditing information such as logon events (success and failure).

The error log is a valuable data point for SQL Server administrators. As an administrator, you must manage the size of the error logs so that you can use them when they are needed. 

The error log file is initialized every time that the instance of SQL Server is started. If the instance of SQL Server has not been restarted for a long time, the error log file can grow large. If many exceptions (for example, access violations) or critical events (for example, SQL Server assertions) occur, these events can generate lots of information that is written to the SQL Server error log.

More Information

Reinitializing SQL Server error logs

You can use the sp_cycle_errorlog stored procedure to reinitialize the error logs periodically. For more information, go to the following page in SQL Server Books Books Online:

Increasing the number of SQL Server error logs

You can increase the number of error logs that are maintained for a specific instance of SQL Server. For more information, click the following article number to view the article in the Microsoft Knowledge Base:
196909 How to increase the number of SQL Server error logs

Limiting the size of SQL Server error logs

In SQL Server 2012 and later versions, you can use the following code to set the maximum size of individual error logs:

USE [master];

GO



EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE',

N'Software\Microsoft\MSSQLServer\MSSQLServer',

N'ErrorLogSizeInKb', REG_DWORD, 5120;

GO


More information

For more information, see the following blog posts by Paul Randal and Jan Kare Lokna:


For additional information about how to monitor the SQL Server error log, see the following Microsoft Developer Network (MSDN) post: 
For more information about the products and tools that automatically check for this condition on your instance of SQL Server and on different versions of the SQL Server product, see the following table.

Rule softwareRule titleRule descriptionProduct versions against which the rule is evaluated
SQL Server 2008 R2 Best Practice Analyzer (SQL Server 2008 R2 BPA)



SQL Server error logs are very big



The SQL Server 2008 R2 Best Practice Analyzer (SQL Server 2008 R2 BPA) provides a rule to detect situations in which the SQL Server error log is large. The SQL Server 2008 R2 BPA supports SQL Server 2008 and SQL Server 2008 R2. 

If you run the BPA tool and encounter the warning "Database Engine - SQL Server error logs are very big," you have to check the size and contents of the error logs for the instance of SQL Server and then set up an appropriate cycle and retention.
SQL Server 2008
SQL Server 2008 R2





SQL Server 2012 Best Practice Analyzer (SQL Server 2012 BPA)

SQL Server error logs are very big
The SQL Server 2012 Best Practice Analyzer (SQL Server 2012 BPA) provides a rule to detect situations in which the SQL Server error log is large. If you run the BPA tool and encounter the warning "DatabaseEngine - SQL Server error logs are very big," you have to check the size and contents of the error logs for the instance of SQL Server and then set up an appropriate cycle and retention.SQL Server 2012






Third-party information disclaimer


Propiedades

Id. de artículo: 2199578 - Última revisión: 1 may. 2014 - Revisión: 1

Comentarios