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).
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.
|
1 2 3 4 |
USE master GO xp_readerrorlog 0, 1, N'Logging SQL Server messages', NULL, NULL,NULL GO |
This query has the following parameters:
- Error log file: value 0 for the current , 1 for Archive#1, 2 for Archive #2
- Logfile type: Value 0 for SQL Server error log, 1 for SQL Server Agent log
- Search string1
- Search string 2
- from time
- end-time
- 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.
Method 2: Using the SERVERPROPERTY() function
We can query the SERVERPROPERTY function as well to identify the location of the SQL Server ERRORLOG.
|
1 |
SELECT SERVERPROPERTY('ErrorLogFileName') AS 'Error log location' |
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.
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.
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.
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.
Its equivalent T-SQL script uses xp_instance_regwrite extended stored procedure and updates the registry value.
|
1 2 3 4 |
USE [master] GO EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'NumErrorLogs', REG_DWORD, 30 GO |
- 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.
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
Its equivalent T-SQL script updates the ErrorLogSizeInKb in the SQL Server registry.
|
1 2 3 4 |
USE [master] GO EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'ErrorLogSizeInKb', REG_DWORD, 1048576 GO |
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.
|
1 2 |
EXEC sp_cycle_errorlog GO |
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.
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
To add the information message, put a tick on the checkbox labeled Information.
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.
Alternatively, use the stored procedure sp_cycle_agent_errorlog to recycle SQL Server agent log files manually.
|
1 2 3 4 |
USE msdb ; GO EXEC dbo.sp_cycle_agent_errorlog ; GO |
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.
I am the author of the book "DP-300 Administering Relational Database on Microsoft Azure". I published more than 650 technical articles on MSSQLTips, SQLShack, Quest, CodingSight, and SeveralNines.
I am 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 my contribution to the SQL Server community, I have been recognized as the prestigious Best Author of the Year continuously in 2019, 2020, and 2021 (2nd Rank) at SQLShack and the MSSQLTIPS champions award in 2020.
Personal Blog: https://www.dbblogger.com
I am always interested in new challenges so if you need consulting help, reach me at rajendra.gupta16@gmail.com
View all posts by Rajendra Gupta
- How to install PostgreSQL on Ubuntu - July 13, 2023
- How to use the CROSSTAB function in PostgreSQL - February 17, 2023
- Learn the PostgreSQL COALESCE command - January 19, 2023