Edit

Manage the SQL Server error log

  • Article

This article describes how to manage the SQL Server error log.

Original product version:   SQL Server
Original KB number:   2199578

Summary

The Microsoft SQL Server error log contains much valuable information that is generated by 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're needed.

The error log file is initialized every time that the instance of SQL Server is started. If the instance of SQL Server hasn't 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.

Reinitializing SQL Server error logs

You can use the sp_cycle_errorlog stored procedure to reinitialize the error logs periodically. For more information, see sp_cycle_errorlog (Transact-SQL).

Configuring the number and size of SQL Server Error Logs

For more information on how you can configure the size and number of SQL Server Error logs for an instance review Configure SQL Server Error Logs.

Reading the error logs

You can read the contents of the SQL Server or SQL Server Agent error log file and filter on keywords by using sp_read_errorlog. For example, you can find log messages in the current SQL Server error log that indicate a database is starting up by running the following command:

EXEC sp_readerrorlog 0, 1, 'database', 'start'

More information

For more information on configuring these values using T-SQL, see the following blog posts by Paul Randal and Jan Kare Lokna:

Third-party information disclaimer

Third-party information disclaimer

The third-party products that this article discusses are manufactured by companies that are independent of Microsoft. Microsoft makes no warranty, implied or otherwise, about the performance or reliability of these products.