Help: SQL Server

I know that most of you have been using sp_readerrorlog for long time. While working with SSMS, I noticed that we have options to put filters on various parameter. I became curious to know what is done in the background.

If we look at code of sp_readerrorlog using sp_helptext system procedure, this is the output.

create 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

Which means that under the cover, xp_readerrorlog is being used by sp_readerrorlog. Coming back to SSMS, here is the snip of information taken from profiler while launching Log File Viewer. I have done some formatting to make it look proper.

-- Below is the way SSMS Reads the ErrorLog. Can be found via profiler

Declare @InstanceName nvarchar(4000),
        @ArchiveID int,
        @Filter1Text nvarchar(4000),
        @Filter2Text nvarchar(4000),
        @FirstEntry datetime,
        @LastEntry datetime

Select  @InstanceName=NULL,
        @ArchiveID=0,
        @Filter1Text='recovery',
        @Filter2Text='mydb',
        @FirstEntry='2014-11-22 07:14:46.930',
        @LastEntry='2014-11-22 07:14:50.020'

EXEC master.dbo.xp_readerrorlog @ArchiveID, 1, @Filter1Text, @Filter2Text, @FirstEntry, @LastEntry, N'asc', @InstanceName 

That gives us a lot of hint about the parameters of xp_readerrorlog. Here is the explanation.

Sample with explanation

I have customized the script so that you can copy paste in your environment and put the values.

Declare @InstanceName nvarchar(4000),
        @LogType int,
        @ArchiveID int,
        @Filter1Text nvarchar(4000),
        @Filter2Text nvarchar(4000),
        @FirstEntry datetime,
        @LastEntry datetime

Select  @InstanceName=NULL,          -- Don't know 🙂
        @LogType = 2,                -- File Type (1 = ERRORLOG OR 2 = SQLAgent)
        @ArchiveID=3,                -- File Extension (0 = Current i.e. ERRORLOG or SQLAgent.out, 1 = ERRORLOG.1 or SQLAgent.1 and so on)
        @Filter1Text='Waiting',      -- First Text Filter
        @Filter2Text=NULL,           -- Second Text Filter
        @FirstEntry=NULL,            -- Start Date
        @LastEntry=NULL              -- End Date

EXEC master.dbo.xp_readerrorlog @ArchiveID, @LogType, @Filter1Text, @Filter2Text, @FirstEntry, @LastEntry, N'asc', @InstanceName 

Here are few samples

Example 1

Read ERRORLOG and apply filter for ‘login failed’ and ‘sa’

Declare @InstanceName nvarchar(4000),
        @LogType int,
        @ArchiveID int,
        @Filter1Text nvarchar(4000),
        @Filter2Text nvarchar(4000),
        @FirstEntry datetime,
        @LastEntry datetime

Select  @InstanceName=NULL,          -- Don't know 🙂
        @LogType = 1,                -- 1 = ERRORLOG
        @ArchiveID=0,                -- File Extension 0 
        @Filter1Text='Login failed', -- First Text Filter
        @Filter2Text='sa',           -- Second Text Filter
        @FirstEntry=NULL,            -- Start Date
        @LastEntry=NULL              -- End Date

EXEC master.dbo.xp_readerrorlog @ArchiveID, @LogType, @Filter1Text, @Filter2Text, @FirstEntry, @LastEntry, N'asc', @InstanceName 

Here is the output

Example 2

In above example, let’s apply date time filer as well. We will filter records only for 05-Dec-2014.

Declare @InstanceName nvarchar(4000),
        @LogType int,
        @ArchiveID int,
        @Filter1Text nvarchar(4000),
        @Filter2Text nvarchar(4000),
        @FirstEntry datetime,
        @LastEntry datetime

Select  @InstanceName=NULL,                  -- Don't know 🙂
        @LogType = 1,                        -- 1 = ERRORLOG
        @ArchiveID=0,                        -- File Extension 0 
        @Filter1Text='Login failed',         -- First Text Filter
        @Filter2Text='sa',                   -- Second Text Filter
        @FirstEntry='2014-12-05 00:00:00',   -- Start Date
        @LastEntry='2014-12-05 23:59:59'     -- End Date

EXEC master.dbo.xp_readerrorlog @ArchiveID, @LogType, @Filter1Text, @Filter2Text, @FirstEntry, @LastEntry, N'asc', @InstanceName 

As expected, the output is similar to Example 1 but with date filter applied.

Example 3

In this example, we are trying to read Read SQLAgent.3 (i.e. LogType = 2 and ArchiveID = 3) and output should be descending date time.

Declare @InstanceName nvarchar(4000),
        @LogType int,
        @ArchiveID int,
        @Filter1Text nvarchar(4000),
        @Filter2Text nvarchar(4000),
        @FirstEntry datetime,
        @LastEntry datetime

Select  @InstanceName=NULL,    -- Don't know 🙂
        @LogType = 2,          -- 2 = SQLAgent
        @ArchiveID=3,          -- File Extension 3 
        @Filter1Text=NULL,     -- First Text Filter
        @Filter2Text=NULL,     -- Second Text Filter
        @FirstEntry=NULL,      -- Start Date
        @LastEntry=NULL        -- End Date

EXEC master.dbo.xp_readerrorlog @ArchiveID, @LogType, @Filter1Text, @Filter2Text, @FirstEntry, @LastEntry, N'DESC', @InstanceName

Here is the output.

Hope this gives you an understanding of neat way of reading SQL Server ERRORLOG and SQLAgent logs.