The SQL Server error log file contains an account of the entire information generated in SQL Server. It contains warnings, informational messages, and information about critical events that take place in SQL Server. Furthermore, important information, like success and failure of logon events user-generated messages also get stored in the SQL Server error log. Since it is a valuable data point for the SQL Server administrators, managing the size of the error logs is the most important thing that needs to be done.
Every time, the SQL Server instance is started, the error log is initialized. In case the SQL Server instance has not been stated from a very long time, the error log can grow to a very large size. However, management of SQL Server error logs helps in maintaining the size of error log under control.
In this write-up, we will discuss some of these methods by which an administrator can manage SQL Server error logs effectively.
Methods for Managing SQL Server Error Logs
There are many methods, which can help the administrators to manage the SQL server error logs effectively. Some of them are:
- Reinitialize SQL Server Error Logs
- Increase Number Of Error Logs
- Limiting Error Log Size
In order to reinitialize the server, the stored procedure sp_cycle_errorlog can be used. This stored procedure closes current error log and like a server restart, cycles error log extension. The new error log file will contain the version and copyright information and a line that will indicate that a new log is created.
Each time, SQL Server is started, the error log file is named to errorlog.1, and then errorlog.1 becomes errorlog.2 and so on. The syntax sp_cycle_errorlog cycles the error log files and that too without stopping and starting the server.
The second method by which an administrator can effectually manage SQL Server error log files is by increasing the number of error log files for a specific instance. There are seven error logs by default in SQL Server, which are stored in the log folder. For better management of error logs, the user might want to create more than seven log files. This can be done by adding a registry key in the registry editor. Before making any changes in the registry, it is advised to create a backup of the entire data.
Browse to the following location in registry editor.
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\MSSQLServer
Create a key with the name NumErrorLogs of value REG_DWORD type to it. Double click on it and change its value data to the number of extra error logs you require in SQL Server.
Limiting the size of error log file is another method with the help of which the SQL Server error logs can be maintained. In SQL Server 2012 and subsequent versions, following mentioned script can be run to manage the size of an individual error log file.
Conclusion
With the help of the above-mentioned parameters, a database administrator can easily manage SQL Server the error log files.
