Reading the SQL Server log files using TSQL

By: Greg Robidoux   |   Read Comments (8)   |   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(255) = NULL,    @p4     VARCHAR(255) = 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

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 6, 1, '2005'

This returns just 8 rows wherever the value 2005 appears.

Example 3

EXEC sp_readerrorlog 6, 1, '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 6, 1, '2005', 'exec', NULL, NULL, N'desc' EXEC master.dbo.xp_readerrorlog 6, 1, '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

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

View all my tips

We Recommend

• Identify location of the SQL Server Error Log file...
• Read the end of a large SQL Server Error Log...
• More SQL Server DBA Tips...

Comments and Feedback:

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.

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 - 8:14:08 AM - apostolp	Read The Tip
Many thanks Greg.

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';

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

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

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

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

Post a Comment or Question

Keep it clean and stay on the subject or we may delete your comment.
Your email address is not published. Required fields are marked with an asterisk (*)

*Name

*Email

Notify for updates Signup for our newsletter

Comments *** NOTE *** - If you want to include code from SQL Server Management Studio (SSMS) in your post, please copy the code from SSMS and paste the code into a text editor like NotePad before copying the code below to remove the SSMS formatting.
*Enter Code

 

Sponsor Information

Find and fix SQL Server problems before they happen - SQL diagnostic manager now with predictive analysis! Get your SQL Server database under version control now! Find out why... Is “blocking” a bad word at your company? Contact the Edgewood SQL Server Consultants for the resolution. Secure column & whole database on all versions and editions of SQL Server with NetLib’s TDE Optimizing SQL Server performance can be a daunting task. Or is it?