Read Error Log Stored Procedure
Contents |
| 2 | 3 | More > Monitoring ProblemOne xp_readerrorlog of the issues I have is that the SQL
Xp_readerrorlog Sql 2014
Server Error Log is quite large and it is not always easy to view the sp_readerrorlog in sql server 2012 contents with the Log File Viewer. In a previous tip "Simple way to find errors in SQL Server error log" you discussed a method
Sp_readerrorlog Filter By Date
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 Server error log files xp_readerrorlog 2014 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]( Integration Services SQL Server Reporting Services InfoCenters Advertisement Home > Technology > Database Administration > Administration Tools > Stored Procedures > View Error Logs View Error Logs Apr 23, 2002 Readers | SQL Server Pro EMAIL Tweet xp_readerrorlog vs sp_readerrorlog Comments 0 Advertisement SQL Server Books Online (BOL) doesn't document a way to view error logs from your application. But two undocumented extended stored procedures in SQL Server 2000 and 7.0—sp_enumerrorlogs and sp_readerrorlog—let you examine error https://www.mssqltips.com/sqlservertip/1476/reading-the-sql-server-log-files-using-tsql/ logs so that you can quickly find and correct errors. Sp_enumerrorlogs (which you can also write as xp_enumerrorlogs—either version of the name is correct) returns the current error-log information, which Table 1, page 14, describes. For example, when you run a statement such as EXEC master..sp_enumerrorlogs you get a result set like the one that Figure 1, page 14, shows. The extended stored procedure sp_readerrorlog (which you can also write as xp_readerrorlog) http://sqlmag.com/stored-procedures/view-error-logs provides the contents of a specified error log, as Table 2 describes. In the syntax sp_readerrorlog \[n\] n is the number of the archive for the current error log (the default number is 0). When you run a statement such as EXEC master..sp_readerrorlog 1 you get a result set like the one that Figure 2 shows. The first four rows are formatted differently than the rest of the rows, and the format of the last column (ContinuationRow) doesn't help you understand that all the returned rows are one record. The first 22 characters in each row provide date and time information. The next 10 characters in each row show the source of the event—either the Server Process ID (SPID) or the inner server process. The rest of the ERRORLOG.1 column is the text of the message. When you're developing automatic applications that work 24 * 7, you need a way to determine which errors require immediate action. Using these stored procedures to analyze error logs can help you launch an error-recovery procedure quickly. —Oleg G. Dantchenko oleg.dantchenko@acs-inc.com Print reprints Favorite EMAIL Tweet Please Log In or Register to post comments. Advertisement Related Articles54 Administration Tips 2 Avoiding the Red Zone 4 Put the Hammer Down 2 A View to Automation 2 A View of Your Ow here for a quick overview of the site Help Center Detailed answers to any questions you might have Meta Discuss the workings and policies of this site About Us Learn more about Stack Overflow the company Business Learn more http://stackoverflow.com/questions/5748779/sql-server-error-logging-from-a-stored-procedure about hiring developers or posting ads with us Stack Overflow Questions Jobs Documentation Tags Users Badges Ask Question x Dismiss Join the Stack Overflow Community Stack Overflow is a community of 6.2 million programmers, just like you, helping http://www.lucasnotes.com/2012/10/querying-sql-server-error-log.html each other. Join them; it only takes a minute: Sign up SQL Server error logging from a Stored Procedure up vote 3 down vote favorite Our application is Windows Service (native .EXE written in C++) that calls stored procedures read error in SQL Server. In most cases errors in stored procedures (in 90% of the cases these errors mean something was wrong in our business logic) are re-thrown as exception and caught by our service. They are then logged in Application Event Log on the computer where our service is running. However, I now have a need to log some of the errors on the SQL Server itself within a stored procedure. Following the paradigm we use for read error log our service I think I can use xp_logevent to save error information in the event log. Is this a recommended approach to log SQL Server errors? FWIW I use SQL Server 2008 sql-server share|improve this question edited Apr 21 '11 at 19:26 John Saunders 138k20178323 asked Apr 21 '11 at 19:22 Joe Schmoe 3352422 add a comment| 4 Answers 4 active oldest votes up vote 6 down vote accepted The How To You can always use RAISEERROR() WITH LOG. Logs to both Windows Application log and the SQL error log.Please note that severity level is key here. There are some limitations and security considerations, but you get some other features also. More details in BOL: http://msdn.microsoft.com/en-us/library/ms178592.aspx The Should you My opinion is that you shouldn't log anything to SQL error log unless it's generated by SQL server itself. Multiple reasons: If your IT or DBA uses log analyzer or any other tool, it may trip an alarm on an application issue, instead of the server issue (this is what they are trying to catch). I never found parsing error logs enjoyable from within SQL server, and I'm not particularly in love with SSMS's way of doing it. My suggestion Use a generic logging stored procedure writing to some error log table. A very nice patter is BEGIN TRY ...do your stuff END TRY BEGIN CATCH get the ERROR_LINE(), ERROR_MESSAGE( while back, I wrote a blog post about reading SQL Server error log using Microsoft Log Parser. That blog post can be found here. There are many ways in which you can query the SQL Server error log. One of them is using the sys.sp_readerrorlog stored procedure. This stored procedure can be located in the master database. It accepts 4 input parameters: @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 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. 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 u
@p1 INT = 0,
@p2 INT = NULL,
@p3 VARCHAR(255) = NULL,
@p4 VARCHAR(255) = NULL)
AS
BEGIN
IF (NOT IS_SRVROLEMEMBER(N'securityadmin') = 1)
Sp_readerrorlog MsdnXp_readerrorlog All Logs