Learn more about SQL Server tools

 

Tutorials          DBA          Dev          BI          Career          Categories          Events          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Events

DBA    Dev    BI    Categories

 

Reading the SQL Server log files using TSQL


By:   |   Read Comments (14)   |   Related Tips: 1 | 2 | 3 | More > Monitoring

Problem

One of the issues I have is that the SQL Server Error Log is quite large and it is not always easy to view the contents with the Log File Viewer.  In a previous tip "Simple way to find errors in SQL Server error log" you discussed a method of searching the error log using VBScript.  Are there any other easy ways to search and find errors in the error log files?

Solution

SQL Server 2005 offers an undocumented system stored procedure sp_readerrorlog.  This SP allows you to read the contents of the SQL Server error log files directly from a query window and also allows you to search for certain keywords when reading the error file.  This is not new to SQL Server 2005, but this tip discusses how this works for SQL Server 2005.

This is a sample of the stored procedure for SQL Server 2005.  You will see that when this gets called it calls an extended stored procedure xp_readerrorlog.

CREATE PROC [sys].[sp_readerrorlog](
   
@p1     INT = 0,
   
@p2     INT = NULL,
   
@p3     VARCHAR(255NULL,
   
@p4     VARCHAR(255NULL)
AS
BEGIN

   IF 
(NOT IS_SRVROLEMEMBER(N'securityadmin'1)
   
BEGIN
      RAISERROR
(15003,-1,-1N'securityadmin')
      
RETURN (1)
   
END
   
   IF 
(@p2 IS NULL)
       
EXEC sys.xp_readerrorlog @p1
   
ELSE
       EXEC 
sys.xp_readerrorlog @p1,@p2,@p3,@p4
END

This procedure takes four parameters:

  1. Value of error log file you want to read: 0 = current, 1 = Archive #1, 2 = Archive #2, etc...
  2. Log file type: 1 or NULL = error log, 2 = SQL Agent log
  3. Search string 1: String one you want to search for
  4. Search string 2: String two you want to search for to further refine the results

If you do not pass any parameters this will return the contents of the current error log.

Here are a few examples:

Example 1

EXEC sp_readerrorlog 6

This statement returns all of the rows from the 6th archived error log.


Example 2

EXEC sp_readerrorlog 61'2005'

This returns just 8 rows wherever the value 2005 appears.

Example 3

EXEC sp_readerrorlog 61'2005', 'exec'

This returns only rows where the value '2005' and 'exec' exist.


xp_readerrrorlog

Even though sp_readerrolog accepts only 4 parameters, the extended stored procedure accepts at least 7 parameters.

If this extended stored procedure is called directly the parameters are as follows:

  1. Value of error log file you want to read: 0 = current, 1 = Archive #1, 2 = Archive #2, etc...
  2. Log file type: 1 or NULL = error log, 2 = SQL Agent log
  3. Search string 1: String one you want to search for
  4. Search string 2: String two you want to search for to further refine the results
  5. Search from start time  
  6. Search to end time
  7. Sort order for results: N'asc' = ascending, N'desc' = descending
EXEC master.dbo.xp_readerrorlog 61'2005''exec', NULL, NULL, N'desc'
EXEC master.dbo.xp_readerrorlog 61'2005''exec', NULL, NULL, N'asc'


Next Steps
  • As you can see this is a much easier way to read the error logs and to also look for a specific error message without having to use the Log File Viewer.
  • Add this to your monitoring routine where this is run daily to search for errors or issues.


Last Update:





About the author











More SQL Server Solutions




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


Get free SQL tips:

*Enter Code refresh code     



Tuesday, January 20, 2015 - 12:33:36 PM - Greg Robidoux Back To Top

Hi Peter,

you can use xp_readerrorlog and use the 5th parameter Start Time.

-Greg


Monday, January 19, 2015 - 9:31:45 PM - Peter Back To Top

Hi,

How to display only the entries from current logs, for today's date only? Thanks.


Tuesday, August 19, 2014 - 1:01:16 PM - Sean P. Tomas Back To Top

Hola David

 

No debes tener el Management Studio pero necesitas una manera que puede corer el SQL que nos enseno.  Perdo, pero no entiendo su pregunta sobre errors.  Que esta buscando in ese caso?

 

-- Sean


Saturday, July 26, 2014 - 1:03:33 AM - David Alfonso Back To Top

Hi,  I would known if I can execute those commads when i dont have Management Studio, and what does mean this errors.

No hablo muy bien el ingles espero puedan responder en español


Monday, March 03, 2014 - 7:23:44 AM - MSSQL DBA Back To Top

How to check SSRS error log using Query ?


Friday, June 21, 2013 - 7:23:24 AM - Jim Curry Back To Top

Great article. Is there a method to search the windows event logs?


Wednesday, February 27, 2013 - 11:57:46 AM - Hillsman Back To Top

Many thanks for this Greg - very useful.

I used your code it to loop through the SQL Server Logs to return information about database restores. I found that this is the only way I could retrieve database restore finish-times from SQL Server (since msdb.dbo.restorehistory only stores the restore start time).

 


Thursday, January 31, 2013 - 7:04:49 AM - Greg Robidoux Back To Top

@Deepu - you could use sp_readerrorlog to get the errors and then use sp_send_dbmail to send the messages.  You would need to setup a job to periodically check the error log.  Stay tuned for a future tip to do what you are requesting.

Here is a tip that show you how to send emails: http://www.mssqltips.com/sqlservertip/2347/send-email-in-a-tabular-format-using-sql-server-database-mail/

Greg


Thursday, January 31, 2013 - 12:40:28 AM - Deepu Back To Top

Can any one help me to reading errorlogs, and send alert mails if any errors found


Wednesday, June 25, 2008 - 12:33:05 PM - Pardo Back To Top

Great tip,

Parameters 6 and 7 are between dates!!!

exec xp_readerrorlog 0, 1,'succeeded','pardo','2008-06-23 10:06:59.250','2008-06-24 16:40:56.790','asc'

It is only for  SQL Server 2005

 Pardo


Tuesday, June 17, 2008 - 5:30:26 AM - hexiaomail Back To Top

This procedure takes 7 parameters:
1.        Value of error log file you want to read: 0 = current, 1 = Archive #1, 2 = Archive #2, etc...
2.        Log file type: 1 or NULL = error log, 2 = SQL Agent log
3.        Search string 1: String one you want to search for
4.        Search string 2: String two you want to search for to further refine the results
5.        Search from  start time
6.        Search to end time
7.        Sort order for results: N'asc' = ascending, N'desc' = descending

--the 5 and 6 paramenters use VARCHAR type,desc
declare @Time_Start varchar(30);
declare @Time_End varchar(30);
set @Time_Start=convert(varchar(30),getdate()-5,25);
set @Time_End=convert(varchar(30),getdate(),25);
EXEC master.dbo.xp_readerrorlog 0, 1, 'Failed', 'login', @Time_Start, @Time_End, N'desc';

--the 5 and 6 paramenters use DATETIME type
declare @Time_Start datetime;
declare @Time_End datetime;
set @time_start=getdate()-5;
set @Time_End=getdate();
EXEC master.dbo.xp_readerrorlog 0, 1, 'Failed', 'login', @Time_Start, @Time_End, N'desc';


Tuesday, April 15, 2008 - 8:14:08 AM - apostolp Back To Top

Many thanks Greg.


Tuesday, April 15, 2008 - 8:01:19 AM - grobido Back To Top

I think the format for SQL Server 2000 is different than SQL Server 2005.

Take a look at this article:

http://vyaskn.tripod.com/sp_readerrorlog_undocumented.htm

Regards,
Greg


Tuesday, April 15, 2008 - 7:18:24 AM - apostolp Back To Top
I was not aware of this functionality but I cannot seem to get the search facility to work in SQLServer 2000. It works fine in SQLServer 2005 but when I run EXEC sp_readerrorlog 1, null, 'master' (EXEC sp_readerrorlog 1, null, 'master'  actually returns an error in SQLServer 2000) 

in SQLServer 2000 it does not narrow the result set and just returns everything. Something similar happens with xp_readerrorlog.

Any ideas ?

Many thanks,

Peter A.


Learn more about SQL Server tools