But things could get more complicated if the Error Log contains lots of records and in those records if you require to swim for the issue which you are looking for.
Even though it provides you some searching and filtering capabilities, it could still be very challenging and time consuming.
However we do have another workaround which might come in handy. That’s to query the Error Logs using T-SQL. This can be done using the system procedure ‘sys.sp_readerrorlog’. This consists with few parameters.
USE [master] GO /****** Object: StoredProcedure [sys].[sp_readerrorlog] Script Date: 24/11/2015 7:11:36 PM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER OFF GO ALTER proc [sys].[sp_readerrorlog]( @p1 int = 0, @p2 int = NULL, @p3 nvarchar(4000) = NULL, @p4 nvarchar(4000) = NULL) as begin if (not is_srvrolemember(N'securityadmin') = 1) begin raiserror(15003,-1,-1, N'securityadmin') return (1) end if (@p2 is NULL) exec sys.xp_readerrorlog @p1 else exec sys.xp_readerrorlog @p1,@p2,@p3,@p4 end
- @p1 –> This represents the error log which you need to inspect (0 ~ Current | 1 ~ Archive #1 etc..)
- @p2 –> Type of the error log which you want to inspect (NULL or 1 ~ Error Log | 2 ~ SQL Agent Log)
- @p3 –> 1st Search Parameter (A value which you want to search the contents for)
- @p4 –> 2nd Search Parameter (A value which you want to search to further refine the result set)
**Please note: Aforementioned parameters are optional. Therefore if you don’t provide any parameters, it will return the whole contents of the current/active Error Log.
Few Examples
1. This will return all entries in the current Error Log
EXEC sys.xp_readerrorlog @p1 = 0
2. This will return all the entries in the current SQL Agent Log
EXEC sys.xp_readerrorlog @p1 = 0, @p2 = 2
3. This will return all the entries in the current SQL Error log where ever the value ‘CLR’ consist.
EXEC sys.sp_readerrorlog @p1=0, @p2=1, @p3='CLR'
4. This will return the entries in the current SQL Error log when the value ‘CLR’ and ‘Framework’ exist.
EXEC sys.sp_readerrorlog @p1=0, @p2=1, @p3='CLR', @p4='Framework'
When we execute the stored procedure ‘sys.sp_readerrorlog’, inside it will call an extended stored procedure which will accept 7 parameters, which is ‘sys.xp_readerrorlog’. The parameter details are as follows:
Param # | Parameter | Details |
1 | Log Number | 0 – Current / 1 – Archive #1 / 2 – Archive #2 etc… |
2 | Log Type | 1 – SQL Error Log / 2 – SQL Agent Log |
3 | Search Text 1 | Search term which will be searched on the Text column |
4 | Search Text 2 | Search term which will be searched on the Text column. **If both search texts are supplied it will return rows containing both texts. |
5 | Start Date | Log entries which the ‘Log Date’ is newer than the date provided. (including the date provided) |
6 | End Date | Log entries which is between the Start Date and End Date |
7 | Sort Order | ASC – Ascending / DESC - Descending |
Eg:
EXEC sys.xp_readerrorlog 0,1,N'',N'', '20151124','20151125','DESC'
I hope this information will help you when you need to query the Error Log in order to troubleshoot an issue.