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.
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.
There are two ways to find out the same. One through SSMS GUI and second method is through T-SQL code.
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.
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!