Query Sql Error Log
Contents |
| 2 | 3 | More > Monitoring xp_readerrorlog sql 2014 ProblemOne of the issues I have is that the
Sp_readerrorlog In Sql Server 2012
SQL Server Error Log is quite large and it is not always easy to xp_readerrorlog all logs view the contents with the Log File Viewer. In a previous tip "Simple way to find errors in SQL Server error log" you
Sql Server Transaction Logs
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? SolutionSQL Server 2005 offers an undocumented system stored procedure sp_readerrorlog. This SP allows you to read the contents of the SQL xp_readerrorlog 2014 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') http://www.lucasnotes.com/2012/10/querying-sql-server-error-log.html stored procedure. This stored procedure can be located in the master database. It accepts 4 input parameters: https://sqlserverrider.wordpress.com/2013/07/28/sql-server-read-error-log-data-using-sp_readerrorlog-system-stored-procedure/ @p1 - integer: This parameter is to specify which error log to read. SQL Server error log would rollover. By default, SQL Server error log would keep a file for the current log and maximum 6 of archived logs (this setting can be changed easily), ERRORLOG, ERRORLOG.1, ERRORLOG.2, ERRORLOG.3, ERRORLOG.4, ERRORLOG5 and ERRORLOG6. ERRORLOG is where SQL Server stores the current error log, ERRORLOG.1 is sql server where SQL Server stores the most recent archived, etc. If we put 0 or null on this parameter, we are querying the current error log (ERRORLOG). 1 would refer to ERRORLOG.1. The same concept would apply to SQL Server Agent error log. @p2 - integer: This parameter is to specify if we want to query the SQL Server Error Log or the SQL Server Agent Error Log. If we enter 1 or null, we are querying the SQL Server Error Log. query sql error However, if we enter 2, then we are querying the SQL Server Agent Error Log. @p3 - varchar(255): We can specify word or phrase that we are looking within the text/message on the SQL Server error log or SQL Server Agent error log. @p4 - varchar(255): We can specify word or phrase that we are looking within the text/message on the SQL Server error log or SQL Server Agent error log. If we enter a word or phrase on @p3 parameter and enter another word or phrase on @p4 parameter, the stored procedure should return error log entries that contain both words/phrases (AND operator). If we leave @p3 blank but enter a word or phrase on @p4, the stored procedure would not filter the error log. It will ignore the @p4 parameter filter. Some Usage Examples The following would return all entries on the current SQL Server error log (ERRORLOG): EXEC sp_readerrorlog or: EXEC sp_readerrorlog 0 or: EXEC sp_readerrorlog NULL, NULL, NULL, NULL The following would return all entries on the current SQL Server Agent error log (SQLAGENT.OUT): EXEC sp_readerrorlog 0, 2 The following would return entry from SQL Server error log when the SQL Server was starting the msdb database (in this case it was part of the server start up): EXEC sp_readerrorlog 0, 1, 'starting', 'msdb' This would returns: Wait, There's more… If we look at the sp_readerrorlog stored procedure code closely, it is actually calling the xp_readererrorlog extended stored procedur
sp_readerrorlog - System StoredProcedure July 28, 2013 Ayyappan Leave a comment Go to comments sp_readerrorlog : this stored procedure is used to read the SQL Server logs and SQL Agent error log data. In this blog post I am going to show you how to use the stored procedure sp_readerrorlog to read log details from SQL Server Logs and SQL Server Agent error log files. Stored procedure usage sp_readerrolog