Help: SQL Server

Sharing my knowlege about SQL Server Troubleshooting Skills

SQL Internals : Useful Parameters for xp_readerrorlog

Posted by blakhani on December 10, 2014


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.

Parameter Name Usage
@ArchiveID Extension of the file which we would like to read.

0 = ERRORLOG/SQLAgent.out

1 = ERRORLOG.1/SQLAgent.1  and so on

@LogType 1 for SQL Server ERRORLOG (ERRORLOG.*)

2 for SQL Agent Logs (SQLAgent.*)
@FilterText1 First Text filter on data
@FilterText2 Another Text filter on data. Output would be after applying both filters, if specified
@FirstEntry Start Date Filter on Date time in the log
@LastEntry End Date Filter on Date time in the log
@SortOrder ‘asc’ or ‘desc’ for sorting the data based on time in log.

 

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.

  • Cheers,
  • Balmukund Lakhani
  • Twitter @blakhani
  • Advertisements

    3 Responses to “SQL Internals : Useful Parameters for xp_readerrorlog”

    1. Malcolm Riley said

      Howdy! Someone in my Facebook group shared this website with us
      so I came to check it out. I’m definitely enjoying the information. I’m book-marking and will be tweeting this to my followers!
      Superb blog and amazing design.

    2. […] Successfully added instant file initialization should mean when you review the log you will not have any MDF showing up in the error log for zeroing. I adapted the sql script for reading the error log in a more filtered manner from this post: SQL Internals Useful Parameters for XP Reader (2014) […]

    3. […] friend and SQL Server senior support engineer Balmukund Lakhani (blog | Twitter) wrote about the different parameters that you can use when calling this system stored procedure. This makes it easy for me to search the […]

    Leave a Reply

    Fill in your details below or click an icon to log in:

    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 )

    Google+ photo

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

    Connecting to %s

     
    %d bloggers like this: