solving sql server problems for millions of dbas and developers since 2006 join MSSQLTips for free SQL Server tips













































   Got a SQL tip?
            We want to know!

Reading the SQL Server log files using TSQL

MSSQLTips author Greg Robidoux By:   |   Read Comments (12)   |   Related Tips: 1 | 2 | 3 | More > Monitoring

Problem
O
ne 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: 4/14/2008


About the author
MSSQLTips author Greg Robidoux
Greg Robidoux is the President of Edgewood Solutions and a co-founder of MSSQLTips.com.

View all my tips


print tip Print  
Become a paid author





join MSSQLTips for free SQL Server tips     



Learn more about SQL Server tools
Post a comment or let the author know this tip helped you.

       All comments are reviewed, so stay on subject or we may delete your comment.

*Name   *Email Notify for updates



       Note: your email address is not published. Required fields are marked with an asterisk (*)


Get free SQL tips:

*Enter Code refresh code     



Tuesday, August 19, 2014 - 1:01:16 PM - Sean P. Tomas Read The Tip

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 Read The Tip

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 Read The Tip

How to check SSRS error log using Query ?


Friday, June 21, 2013 - 7:23:24 AM - Jim Curry Read The Tip

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


Wednesday, February 27, 2013 - 11:57:46 AM - Hillsman Read The Tip

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 Read The Tip

@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 Read The Tip

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 Read The Tip

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 Read The Tip

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 Read The Tip

Many thanks Greg.


Tuesday, April 15, 2008 - 8:01:19 AM - grobido Read The Tip

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 Read The Tip
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.




 
Sponsor Information







Copyright (c) 2006-2014 Edgewood Solutions, LLC All rights reserved
privacy | disclaimer | copyright | advertise | about
authors | contribute | feedback | giveaways | free t-shirt | user groups | community | events | first timer?
Some names and products listed are the registered trademarks of their respective owners.