未加星标

Reading and Searching a Large Error Log File

字体大小 | |
[数据库(mssql) 所属分类 数据库(mssql) | 发布者 店小二04 | 时间 2016 | 作者 红领巾 ] 0人收藏点击收藏

Sometimes the SQL Server ERRORLOG file can become very large. This happens typically when you have turned on auditing for successful and failed logins. Additionally if you don’t restart SQL Server very often, or don’t recycle the ERRORLOG these files can become quite large; so large that it becomes difficult to browse the file looking for a period of time, or error message string. When the error log file is large, using some text editor or the GUI interface within SQL Server Management Studio to browse through the different error log files can be difficult, downright frustrating, and sometime impossible.

To simplify the reading of the large error log file, and provide a little less frustrating process you can use the undocumented extended stored procedure called “xp_readerrorlog”. This extended stored procedure allows you to search those large error log files. This extended stored procedure can read both the SQL Server ERRORLOG and the SQL Agent log files. Since this extended stored procedure is undocumented there is no official documentation from Microsoft explaining how to use this stored procedure. But if you use your favorite internet search engine looking for information about xp_readerrorlog there are a number of posts that explain how the extended store procedure works, and the parameters you can use to search your large ERRORLOG file.

There are seven different parameters you can pass to xp_readerrorlog extended stored procedure:

Parm 1 = Identifies the error log file that you would like read. Set this parm to 0 if you’d like to read the current error log. Or you can set in to either 1, 2, 3, etc. to read one of the historical error log files.

Related Articles Masking Your Data with SQL Server 2016 Managing SQL Server Backup and Restore History Information

Parm 2: Identifies which error log to search. 1, or null for ERRORLOG, or 2 for the SQL Agent log

Parm 3 : The first string you want to search for in the error log file.

Parm 4: The second string you want to search for in the error log file.

Parm 5: The start time constraint on searching.

Parm 6: The end time constraint on searching.

Parm 7: Sort order of the output (asc, desc)

Let’s look at a couple of different common uses for this extended stored procedure.

First let’s assume you want to read the current ERRORLOG file and display all the log records between a start time and an end time. To accomplish this you run the xp_readerrorlog process using the following code:

--Find ERROR log record between 2016-09-20 14:34 and 2016-09 15:50 EXEC xp_readerrorlog 0, 1, null, null, '2016-09-20 14:34', '2016-09-20 15:00 ', 'asc'

Or, if you wanted to search for a specific string of text like ‘error’, then you would run this code:

--Find ERRORLOG records with the word ‘error’ in the message text in the ERRORLOG file EXEC xp_readerrorlog 0, 1, error, null, null, null, 'asc'

You can even search for two different character strings, like ‘error’ and ‘Program’ by running the following query:

--Find ERORRLOG records the word ‘error’ and ‘Program’ are in the message text in the ERRORLOG file EXEC xp_readerrorlog 0, 1, error, Program, null, null, 'asc'

See all articles by Greg Larsen

本文数据库(mssql)相关术语:熊片数据库 mssql数据库 oracle数据库 pubmed数据库 access数据库 万方数据库

主题: SQLSQL Server
分页:12
转载请注明
本文标题:Reading and Searching a Large Error Log File
本站链接:http://www.codesec.net/view/479900.html
分享请点击:


1.凡CodeSecTeam转载的文章,均出自其它媒体或其他官网介绍,目的在于传递更多的信息,并不代表本站赞同其观点和其真实性负责;
2.转载的文章仅代表原创作者观点,与本站无关。其原创性以及文中陈述文字和内容未经本站证实,本站对该文以及其中全部或者部分内容、文字的真实性、完整性、及时性,不作出任何保证或承若;
3.如本站转载稿涉及版权等问题,请作者及时联系本站,我们会及时处理。
登录后可拥有收藏文章、关注作者等权限...
技术大类 技术大类 | 数据库(mssql) | 评论(0) | 阅读(41)