Rajendra Gupta
Set Log file maximum size

How to manage SQL Server logs effectively

October 8, 2021 by

This article covers an overview of SQL Server logs for monitoring and troubleshooting issues in SQL Server.

Introduction

The logs are the best resources for a database administrator in troubleshooting any issues. These issues can be related to server configuration, startup, recovery, performance, trace flags, deadlocks, IO, or Checkpoint delay. For example, suppose your SQL Server instance restarted due to unknown reasons, and after startup, SQL Services are up; however, your application cannot access the database. Therefore, to investigate issues, you can look at the latest SQL Server logs and monitor the database recovery process and estimated time in completion.

The database administrator can also configure SQL Server for additional logging into the error logs. For example, we can enable a trace flag to capture deadlocks information. The DBA should review these logs proactively to look for potential problems. You can identify information such as backup failure, login failure, IO errors by reviewing logs. These error logs are great to look for existing or potential problems in SQL Server instances.

SQL Server logs are known as SQL Server Error logs. This error log has informational, warning, and critical error messages. You can view a few of these logs in the Windows event viewer logs as well. However, it is recommended to use SQL Server logs to get detailed information.

SQL Server Logs and its location

Once you connect to a SQL Server instance in SSMS, navigate to Management -> SQL Server Logs. As shown below, it has the current log and six archive logs ( Archive#1 to Archive #6).

SQL Server Logs location

Method 1: Using the xp_readerrorlog extended procedure

The current logs are the latest error log file, and you can use them to view recent activity since SQL Server starts or manual log file recycling. SQL Server error log is a text file stored in the log directory of SQL Server instance. You can use the extended procedure xp_readerrorlog to find out the current location of the error log.

This query has the following parameters:

  1. Error log file: value 0 for the current , 1 for Archive#1, 2 for Archive #2
  2. Logfile type: Value 0 for SQL Server error log, 1 for SQL Server Agent log
  3. Search string1
  4. Search string 2
  5. from time
  6. end-time
  7. Sort results – Ascending (N’ASC) or descending (N’Desc)

For my demo instance, the error log file location is C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\Log\ERRORLOG.

xp_readerrorlog extended procedure

Method 2: Using the SERVERPROPERTY() function

We can query the SERVERPROPERTY function as well to identify the location of the SQL Server ERRORLOG.

SERVERPROPERTY() function

Method 3: Using the SQL Server Configuration Manager

Alternatively, open SQL Server Configuration Manager and view startup parameters. The log file location is specified using the -e switch.

SQL Server Configuration Manager

You can browse the log directory and view the current archive error log files. These error logs can be opened in a text editor such as Notepad or Visual Studio Code.

View logs in the LOG directory

Configure the number of SQL Server Log files and their size

By default, SQL Server maintains a current and six archived log files. To validate the value, right-click on the SQL Server Logs folder in SSMS and Configure.

Configure the number of SQL Server Log file and their size

SQL Server logs all information in the current log file irrespective of log file size. On a busy system or instance with many errors, you might find it challenging to view the log file in SSMS. SQL Server creates a new log file and archives the current file in the following case.

  • During SQL Service restart
  • Manual error log recycle

However, if you restart servers frequently due to unknown reasons, you might lose all historical archive log data because it maintains only six archive logs. Since the error contains valuable information that can help you troubleshoot, we might not want to lose this crucial data. Instead, we might want to retain log files on a critical production system for a week or even a month.

SQL Server allows the configuration of up to 6 to 99 error log files. We cannot specify a value less than six because, in any case, it maintains six archive error logs.

To change the default number of error log files, put a check on the box labeled – “Limit the number of error log files before they are recycled”. For example, the following screenshot shows a maximum of 30 error log files.

Limit the number of error log files before they are recycled

Its equivalent T-SQL script uses xp_instance_regwrite extended stored procedure and updates the registry value.

  • Note: SQL Services must be restarted so that these changes are in effect

As stated earlier, by default, the error log size is unlimited. For example, if you do not start SQL Server for a longer period and do not manually recycle log files, this file will grow huge. Therefore, in the error log configuration, value 0 shows log size is unlimited.

Log file size

You can specify the size in KB to limit the error log size as per your requirement. For example, here, we limit log file size to 1 GB

Set Log file maximum size

Its equivalent T-SQL script updates the ErrorLogSizeInKb in the SQL Server registry.

Manual recycle error logs

SQL Server allows recycling error logs manually to manage them effectively. For example, suppose you increased the number of error log files to 30. Thus, we can create a SQL Server agent job that recycles error logs at midnight. This way, we have an error log file for each day unless SQL Service is restarted in between. To recycle manually, execute the system stored procedure sp_cycle_errorlog. The user with the sysadmin fix server role can execute this stored procedure.

SQL Server Agent log file

SQL Server agent also has a separate error log similar to the SQL Server logs. You can find it under the SQL Server Agent – > Error logs folder.

Manual recycle error logs

Right-click on the Error log folder and Configure. It gives the agent error log location and agent log level.

The agent log file extension is *.OUT and stored in the log folder as per default configuration. For example, in my system, the log file directory is C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\Log\SQLAGENT.OUT.

By default, agent log file logs errors and warnings; however, we can include information messages:

  • Warning messages: These messages provide information about potential problems. For example, “Job X was deleted while it was running”
  • Error message: It gives information that requires immediate intervention of a DBA, such as being unable to start a mail session

SQL Server Agent logs

To add the information message, put a tick on the checkbox labeled Information.

Agent log level

SQL Server uses up to 9 SQL Server agent log files. The current file name is SQLAGENT.OUT. The file with extension .1 indicates the first archived agent error log. Similarly, an extension .9 indicates the 9th (oldest) archived error log.

SQL Server agent log files are recycled each time SQL Server Agent is restarted. To do it manually, right-click on the Error Logs folder and Recycle.

Recycle agent logs manually

Alternatively, use the stored procedure sp_cycle_agent_errorlog to recycle SQL Server agent log files manually.

The stored procure archives the current agent error log using the following process:

  • A new current agent error log is created
  • Current Agent error log SQLAgent.out coverts to SQLAgent.1
  • SQLAgent.1 converts to SQLAgent.2

Conclusion

The SQL Server error log file has information, warning, and critical messages of an instance. It is helpful for troubleshooting, audit logins (success, failure). The database administrator can configure the number of required archived error logs and directories to store these files.

You should proactively look for messages getting logged as part of your daily and weekly health check reports.

Rajendra Gupta
Installation, setup and configuration, Maintenance, Transaction log

About Rajendra Gupta

As an MCSA certified and Microsoft Certified Trainer in Gurgaon, India, with 13 years of experience, Rajendra works for a variety of large companies focusing on performance optimization, monitoring, high availability, and disaster recovery strategies and implementation. He is the author of hundreds of authoritative articles on SQL Server, Azure, MySQL, Linux, Power BI, Performance tuning, AWS/Amazon RDS, Git, and related technologies that have been viewed by over 10m readers to date. He is the creator of one of the biggest free online collections of articles on a single topic, with his 50-part series on SQL Server Always On Availability Groups. Based on his contribution to the SQL Server community, he has been recognized with various awards including the prestigious “Best author of the year" continuously in 2020 and 2021 at SQLShack. Raj is always interested in new challenges so if you need consulting help on any subject covered in his writings, he can be reached at rajendra.gupta16@gmail.com View all posts by Rajendra Gupta

615 Views