Disclaimer: Hey Guys, this post contains affiliate link to help our reader to buy best product\service for them. It helps us because we receive compensation for our time and expenses.

Introduction:

Why SQL server errorlog files are extremely require, coz it captures system and user related errors and to troubleshoot the issue or as a pro-active approach error logs should be investigated and keep on checking on regular basis.

Errorlog Path:

There are two ways to find out the same. One through SSMS GUI and second method is through T-SQL code.

Through t-sql:

SELECT SERVERPROPERTY('ErrorLogFileName') AS 'Error log file location';

You can browse to the directory and it shows available error log files:

• ErrorLog: Current error log file
• ErrorLog.1: Archive error log file 1
• ErrorLog.2: Archive error log file 2 and so on

The parameters you can use with XP_READERRRORLOG or sp_readerrorlog are mentioned below for your reference:

1. Value of error log file you want to read: 0 = current, 1 = Archive #1, 2 = Archive #2, etc…
2. Log file type: 1 or NULL = error log, 2 = SQL Agent log
3. Search string 1: String one you want to search for
4. Search string 2: String two you want to search for to further refine the results
5. Search from start time
6. Search to end time
7. Sort order for results: N’asc’ = ascending, N’desc’ = descending

Query to list error log and their sizes:

EXEC sys.sp_enumerrorlogs; --where archive 0 from the result is your current file.

Example:

Reads current SQL Server error log and search for multiple conditions:

This query searches for text Database and Initialization in the current SQL Server error logs. Both the conditions should satisfy to return results:

EXEC xp_ReadErrorLog 0, 1, N'login',N'user'

Note: User N’ ‘ for string search or double quote.

Read current SQL Server error log for a specific duration:

EXEC xp_readerrorlog 
    0, 
    1, 
    N'backup', 
    N'', 
    N'2022-08-07 00:00:01.000', 
    N'2022-08-07 09:00:01.000',
N'desc'

Read current SQL Server error log for a specific duration, multiple conditions using variables:

We can use the following query for specifying the required parameter in the variable and use these variables in the command xp_readerrorlog.

It uses the following conditions:

• Search keywords Backup and Master
• Specify start and end date

DECLARE @logFileType SMALLINT= 1;
DECLARE @start DATETIME;
DECLARE @end DATETIME;
DECLARE @logno INT= 0;
SET @start = '2019-11-07 00:00:01.000';
SET @end = '2019-11-07 09:00:00.000';
DECLARE @searchString1 NVARCHAR(256)= 'Backup';
DECLARE @searchString2 NVARCHAR(256)= 'Master';
EXEC master.dbo.xp_readerrorlog 
     @logno, 
     @logFileType, 
     @searchString1, 
     @searchString2, 
     @start, 
     @end;

Hope this helps!