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.
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.
Verifying Instant File Initialization – SQL Barbarian said
[…] 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) […]
Reviewing the SQL Server Error Logs to Find the Two Most Common Keywords That Can Cause Availability Issues | Edwin M Sarmiento said
[…] 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 […]
Reviewing the SQL Server Error Logs to Find the Two Most Common Keywords That Can Cause Availability Issues – Learn SQL Server High Availability & Disaster Recovery said
[…] friend and SQL Server senior escalation 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 […]