Reading SQL server error logs and its configuration

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.

InterServer Web Hosting and VPS
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!

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s