Navigating SQL Server Error Logs

Navigating SQL Server Error Logs
3.9 (77.14%) 7 votes

Introduction

One of the key skills you need as a database administrator or an IT person is generally the ability to monitor systems very carefully. Lack of this key skill can lead to misdiagnosis when troubleshooting issues. SQL Server exposes a number of tools that can aid the DBA in troubleshooting problems that occur in production. The SQL Server Error Log and the SQL Server Agent Log are two of the most important facilities for troubleshooting SQL Server. In this article, we shall explore the ways we can manipulate the Server and Agent logs.

Default Behavior

By default, SQL Server keeps the last six Error Logs and the last nine Agent logs in the instance. By default, a new log file is generated each time the instance of the agent is restarted. The number of log files kept can be modified for the SQL Server logs using the statement of the equivalent GUI below (Fig. 1). To open this GUI in Object Explorer, right-click Management, select SQL Server Error Log and click Configure.

SQL Server Error Log

Fig 1. Last Six Error Logs

Each log file is generated upon restart and physically stored in C:\Program Files\ Microsoft SQL Server\MSSQL14.<InstanceID>\MSSQL\Log along with trace files and dump files. SQL Server Error Logs are named ERRORLOG.x (where x is a number) while SQL Agent Logs are named SQLAGENT.x.

During the instance startup, the engine writes key information to the Error log which will include:

SQL Server Version details System Manufacturer

  • SQL Server Process ID
  • Port Number in use
  • Authentication Mode
  • Registry Startup Parameters
  • Service Account
  • Size of CPU and Memory detected
  • Status of Key options e.g. Large Pages, Buffer Pool Extension, In-Memory OLTP etc.

This information can be very useful when you just want to quickly find out about any of them from the instance that is introduced to you.

Configuring Error Log Options

As mentioned earlier, certain aspects of the SQL Error Log and Agent log are configurable. In my environment, we set NumErrorLogs to 30 because we want to have the last thirty days of logs preserved on a daily basis. We ensure that each log file contains the data of a day by running sp_cycle_errorlog (and sp_cycle_agent_errorlog) at midnight using an Agent Job. Listing 3 shows the details of this job. These stored procedures can also be quite useful for explicitly cycling the error log when you want to reproduce a problem and watch it unfold (i.e. before escalating to SQL Profiler or Extended Events).

Maximum number of error log files

Fig 2. Setting Maximum Number of Error Log Files

The SQL Agent Error Log can be configured to some level of detail by right-clicking SQL Server Agent, and then Error Logs and select Configure from the menu. Listing 2 and Fig. 2 show how to modify the logging level.

Configure error logs

Fig 3. Setting SQL Agent Error Log Options

“Pouring” Through Logs

Familiarizing oneself with the error log is useful for identifying such things as the items listed in the second section or monitoring such events as the recovery of a large database. But when searching for a specific item such as login failures or similar errors hidden in the sea of logs, you may want to use the filter or search capability.

Log File Viewer

Fig 4. Filter and Search

The search would be useful when looking for a specific error number or text. The search returns the occurrences of the item required by line at a time. When you need to narrow down the error log using specific criteria, use the filter. You can filter by connection, date or general details.

Search

Fig 5. Search Criterion

Filter

Fig 6. Filter Criterion

When the Instance Fails

One good question a keen reader would ask is what happens when SQL Server fails to start? How do we examine the logs? Well, it so happens that besides the fact that the error logs are written to flat files the path of which was earlier specified, you can also view the Server and Agent logs in Windows Event Viewer. All you need to do is filter the Event Viewer Application Logs for events with a source MSSQLSERVER. In more advanced environments, SQL Server logs, like any other Windows Logs, can be stored in a third-party tool like SEIM or Splunk. This makes things so much easier. Starting with SQL Server 2012, it is also possible to view offline error logs from another instance entirely, using registered servers as well as using WMI and WQL.

Filter current log

Fig 7. Viewing SQL Server Logs in Event Viewer

Conclusion

The SQL Server Error Log can be considered the starting point for troubleshooting common errors. Other logs, such as trace files generated by SQL Trace or captured by SQL Profiler or Extended Events Session give far more details on what’s happening inside the engine. There are also specific logs like those related to Database Mail or Replication. While these are all very useful for troubleshooting problems in SQL Server, I would recommend SQL Server Error Logs as a great starting point.

References

Viewing the SQL Server Error Log

Viewing Offline Log Files

Sp cycle error log

Sp cycle agent error log

SQL Server Default Installation Path

 

Kenneth Igiri

Kenneth Igiri

Kenneth Igiri is a Database Administrator with eProcess International S.A., Ecobank Group's Shared Services Centre. Kenneth has over eight years' experience with SQL Server and Oracle databases as well as related technologies. His interests include database performance, HADR, and recently, Cloud.
Also, Kenneth teaches at Children's Church and writes fiction. You can connect with Kenneth via his blog or social network account.
Kenneth Igiri

Latest posts by Kenneth Igiri (see all)