SQL Server error logs | Intro | Configuration | Recycle | Querying

SQL SERVER ERROR LOGS

Introduction

In the general case, error logs are useful to troubleshoot system problems. Similarly, SQL Server Error Logs also useful in troubleshooting issues with the database server.  

Error Logs maintains events raised by the SQL Server database engine or Agent. It contains both system and user-defined events data. 

Types of error logs

1. SQL Server Log 
2. Agent Log

By default following events are recorded in error logs.

1. SQL Server startup events 
2. Database recovery and Maintenance related activities 
3. Backup and restore information
4. User-defined error message which has WITH LOG clause
5. Profiler and trace flags turning start or stop 
6. Other

Error logs are present in the LOG folder of the respective instances. The default location is "<Program-Files>\Microsoft SQL Server\<INSTANCE_NAME>\LOG\". Using SERVERPROPERTY() command to know the current error log file location

SELECT SERVERPROPERTY('ErrorLogFileName') AS 'Err_log_file_loc';

SQL Server by default maintains a backup of previous six logs, naming each archived log file sequentially such as ( physical : "ERRORLOG.<seq_no>"; Logical : "Archive #<seq_no> - timestamp" ). The current error log file named ERROR ( logical: "Current - timestamp" ).  

• 0 - current error log
• 1-6 - Archive log files

Configuring

In SQL Server error logs configuration,  

• Limit the number of error logs to be archived. Maximum error logs can configure up to 99. 
• Set the maximum size of the error log file in kb. Value 0 means the log file size is unlimited. 

To configure 

• Go to Object Explorer ->  Expand the respective instance
• Management
• Right-click on SQL Server Logs
• Configure
• Select checkbox " Limit the number of error log files..." 
• Set the Maximum number of logs value.
• Ok

Recycle

By default when the server was restarted the error logs are recycled automatically. We can recycle error log using sp_cycle_errorlog.

Querying

Query to list error log and their sizes

exec sp_enumerrorlogs

Here Archive # = Error Log file number ( 0 - current ; 1 - archive #1 ; ..) ; Date = timestamp of last entry in that file ; Log file Size (Byte) = Error log file size in bytes

SQL Server error logs can read using different methods such as SSMS and T-SQL queries. 

Using T-SQL

we can read error logs using below procs

• sp_readerrorlog
• xp_readerrorlog

SP_READERRORLOG

syntax: sp_readerrorlog <log_no>,<log_file_type>,<filter>

log_no: Error log number ( 0 = current  ; 1 = Archive #1, 2 = Archive #2..  )

log_file_type: 1 or null - SQL Server log ; 2- Agent log

filter: Search keyword

-- List all data in the current error log 

exec sp_readerrorlog 0 -- Here 0 refers current error log

-- List only agent log in the current error log

exec sp_readerrorlog 0,2 -- Here 2 refer agent log

-- In agent search keyword "startup" in the current log 

exec sp_readerrorlog 0,2,'startup'

Note: To execute these procedures, either SecurityAdmin server-level role or the View Server State permission is needed. 

XP_READERRORLOG

syntax: xp_readerrorlog <log_no>,<log_file_type>,<filter_1>,<filter_2>,<start_date>,<end_date>,<sort_order>

log_no: Error log number ( 0 = current  ; 1 = Archive #1, 2 = Archive #2..  )

log_file_type: 1 or null - SQL Server log ; 2- Agent log

filter_1: Search keyword

filter_2: Search keyword ( additional search filter. both conditions should be statisfied to execute { filter_1 and filter_2 }) 

start_date , end_date: Filter logs between start and end dates.

sort_order: ASC (Ascending) or DSC (descending) 

-- Searches for text warning in the current error log

exec xp_readerrorlog 0,1,N'warning'

-- Searches for text "Error" and "Severity: 16" in the current log between dates 2020-05-01 to 2020-05-10

exec xp_readerrorlog 0,1,N'Error',N'Severity: 16', N'2020-05-01 00:00:01.000', N'2020-05-10 00:00:01.000', 'ASC'

Using SSMS 

To read error logs using SSMS, we need to connect the instance and navigate to Management. Expand the Management folder, followed by SQL Server logs.

In the SQL Server Logs folder, you can see the logs - Current and Archive

Double-click on the desired error log file and it will open the error log in a separate window

Problem with accessing error log

• Error logs are not recycled due to log recycling agent job is not available or not running. 
• Enabling auditing or log backups also some time cause to grow log size huge.

Conclusion

This article helps you to understand the error logs and steps to configuring and methods for reading error logs and recycling techniques. 

That's all about  SQL Server Error Logs. 

Let me know if you have any questions, suggestions, or comments.