Read SQL Server error Log and SQL agent log

Often DBA’s need to quickly look at sql server error log and agent logs to find something speicific, and it takes awful lot of time to go through a huge error log file; and you want to get the information quickly;

This is where you can use my script to my information in different ways using a simple query.

xp_readerrorlog is an extension to the sp_readerrorlog ( accepts 4 paramenters) and is extended to provide more filters and search conditions, with 7 parameters;

Paramteres are as below:

  1. Value of error log file you want to read: 0 = current log, 1 = Archive #1, 2 = Archive #2, etc…
  2. Log file type: 1 or NULL = sql server error log, 2 = SQL Agent log
  3. Search string 1: String one you want to search for, eg:- you want to search for database
  4. Search string 2: String two you want to search for to further refine the results; eg:- you want to filter and display only the error messages.
  5. Search from start time : this parameter can be used to filter out the log and fetch log only starting at the start time; best used with end time to see errors over a datetime
  6. Search to end time: this parameter is specified to read the error log upto end time
  7. Sort order for results: N’asc’ = ascending, N’desc’ = descending

To Read the current sql server error log file and search for a specific word (for eg:- Databasename) in the log file use

EXEC xp_readerrorlog 0, 1,’Databasename’,’error’
GO

This scans through the current sql server error log and searches for ‘Databasename’ and filters the results by ‘error’; very handy.

To read the current sql server error log file and see logs over the last 30 mins of time run the below query:

DECLARE @start DATETIME, @end DATETIME
SET @start=DATEADD(MINUTE,-30,GETDATE());
SET @end=GETDATE();

EXEC xp_readerrorlog 0, 1,NULL,NULL,@start,@end,’Desc’
GO

This reads the current sql server error log and returns the sql server error logs for the last 30 mins; perfect if you want to use this like a snipet or a webpart on your dashboard to display current error messages.

To Read the sql server agent error log all you need to do is specify the second parameter as 2.

EXEC xp_readerrorlog 0, 2
GO

This entry was posted in SQL Server, sql server error log, T-SQL Scripts, Tricks and tagged , , , . Bookmark the permalink.

Leave a Reply