Click here to Skip to main content
15,177,535 members
Articles / Programming Languages / T-SQL
Technical Blog
Posted 25 Nov 2015

Tagged as

Stats

10K views
11 bookmarked

Analyzing SQL Server Error Logs / Agent Logs using T-SQL

Rate me:
Please Sign up or sign in to vote.
4.97/5 (10 votes)
25 Nov 2015CPOL3 min read
Analyzing SQL Server Error Logs / Agent Logs using T-SQL

Even though we design our SQL Scripts with the best methods using best practices, or configure the SQL Server to perform correctly and in the optimized manner, you cannot prevent things going wrong. Luckily, SQL Server does a great job on logging all the issues which we will be encountering during the course. Things could have been worse if you need to go through the error log file using only a text editor like the ‘Note Pad’ application (Favorite text editor of majority people). But fortunately, SQL Server had provided us some help when you need to dig deep into Error Log.

image

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.

image

Even though it provides you some searching and filtering capabilities, it could still be very challenging and time consuming.

image

image

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 of a few parameters.

SQL
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
  1. @p1 –> This represents the error log which you need to inspect (0 ~ Current | 1 ~ Archive #1 etc..)
  2. @p2 –> Type of the error log which you want to inspect (NULL or 1 ~ Error Log | 2 ~ SQL Agent Log)
  3. @p3 –> 1st Search Parameter (A value which you want to search the contents for)
  4. @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:
    SQL
    EXEC sys.xp_readerrorlog @p1 = 0
  2. This will return all the entries in the current SQL Agent Log:
    SQL
    EXEC sys.xp_readerrorlog @p1 = 0, @p2 = 2
  3. This will return all the entries in the current SQL Error log wherever the value ‘CLR’ exists.
    SQL
    EXEC sys.sp_readerrorlog @p1=0, @p2=1, @p3='CLR'

    image

  4. This will return the entries in the current SQL Error log when the value ‘CLR’ and ‘Framework’ exist.
    SQL
    EXEC sys.sp_readerrorlog @p1=0, @p2=1, @p3='CLR', @p4='Framework'

    image

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

Example

SQL
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.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Manjuke Fernando
Technical Lead Air Liquide Industrial Services (Singapore)
Singapore Singapore
My passion lies in building business intelligence and data-based solutions, writing about things I work with and talking about it. New technologies relevant to my line of work interest me and I am often seen playing with early releases of such technologies.

My current role involves architecting and building a variety of data solutions, providing database maintenance and administration support, building the organization’s data practice, and training and mentoring peers.

My aspiration over the next several years is to achieve higher competency and recognition in the field of Data Analytics and move into a career of data science.


Specialities: SQL Server, T-SQL Development, SQL Server Administration, SSRS, SSIS, C#, ASP.Net, Crystal Reports

Comments and Discussions

 
-- There are no messages in this forum --