Querying SQL Server Error Log

SQL Server error log offers good way to obtain information when troubleshooting SQL Server related problem. A 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:

1. @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.
2. @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.
3. @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.
4. @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 procedure. The xp_readerrorlog actually accepts more input parameter than the 4 input parameters described above. The following blog article described the parameters that xp_readerrorlog would accept. Basically it would accept 3 additional parameters:

1. Log date from range – Date time: this parameter would help to filter the log entries from a specific time period.
2. Log date to range – Date time: this parameter would help to filter the log entries to a specific time period.
3. Ascending or Descending – Varchar: this parameter can be use to specify the sorting order of the log entries based on the log date. Enter ‘asc’ for ascending order, and ‘desc’ for descending order.

So, for example, if we want to get the list of current SQL Server error log entries between 6:27 PM and 6:28 PM today (7th October 2012), and list the log entries in the descending log date order, I can use the following query:

EXEC xp_readerrorlog 0, 1, NULL, NULL, '2012-10-07 18:27', '2012-10-07 18:28', 'desc'

Related Notes:

• Querying SQL Error Log with Microsoft LogParser
• Querying SQL Server Agent Error Log with Microsoft LogParser

Querying SQL Server Agent Error Log with Microsoft LogParser

Last week I wrote about using Microsoft LogParser to query entries in SQL Error Log. You can read more about it here.

Normally, we would read or query SQL Agent Error Log using SQL Log Viewer. But just as SQL Error Log, SQL Server Agent Error Log is also text-based log. Thus, we can also use Microsoft LogParser to query the log(s).

What is SQL Server Agent Error Log?

SQL Server Agent Error Log contains information, warning or error messages related specifically to SQL Server Agent, information such as when SQL Server Agent service is started or stopped. By default, SQL Server Agent Error Log can be found under the following directory: C:\Program Files\Microsoft SQL Server\MSSQL.x\MSSQL\LOG\. It has file name of SQLAGENT.OUT or SQLAGENT.x.

Using Microsoft LogParser to Read SQL Server Agent Error Log

To output all of the SQL Server Agent Error Logs into a datagrid format, you can use the following command with Microsoft LogParser:

LogParser "SELECT TO_TIMESTAMP(STRCAT(STRCAT(Date,' '), Time), 'yyyy-MM-dd hh:mm:ss') AS DateTime, Message INTO DATAGRID FROM 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\SQLAGENT*'" -i:TSV -iSeparator:spaces -headerRow:OFF -iHeaderFile:C:\SQLAgentLogHeader.txt

Some explanations on the option used:

-i:TSV	This is to indicate that the input file used is a TSV (Tab Separated Values) file
-iSeparator:spaces	This is to specify that we use spaces as the separator between values
-iHeaderFile:C:\SQLAgentLogHeader.txt	The SQLAgentLogHeader.txt file can be downloaded from the following location. If you open one of the SQL Server Agent Error Log, you would notice that it does not have any column header. What this option does is that it tell LogParser to use SQLAgentLogHeader.txt as the header format file. We can use the header specify in the header format file in our SQL query.
-i:headerRow:OFF	This just to tell LogParser that the first row of the input file is not header.
-o:DATAGRID	This is to indicate that we want the output into datagrid format

Additional Examples

To query entries in SQL Server Agent Error Log that are older than 1st January 2010, you can use the following command:

LogParser "SELECT TO_TIMESTAMP(STRCAT(STRCAT(Date,' '), Time), 'yyyy-MM-dd hh:mm:ss') AS DateTime, Message INTO DATAGRID FROM 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\SQLAGENT*' WHERE DateTime < TO_TIMESTAMP('01/01/2010', 'MM/dd/yyyy')" -i:TSV -iSeparator:spaces -headerRow:OFF -iHeaderFile:C:\SQLAgentLogHeader.txt

To find out when SQL Server Agent has been stopped:

LogParser "SELECT TO_TIMESTAMP(STRCAT(STRCAT(Date,' '), Time), 'yyyy-MM-dd hh:mm:ss') AS DateTime, Message INTO DATAGRID FROM 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\SQLAGENT*' WHERE Message LIKE '%SQLSERVERAGENT stopping%'" -i:TSV -iSeparator:spaces -headerRow:OFF -iHeaderFile:C:\SQLAgentLogHeader.txt

Querying SQL Error Log with Microsoft LogParser

What is Microsoft LogParser?

I've been using Microsoft LogParser for a while. It is a great little (yet powerful) tool to query text-based data logs. It is very flexible. It can be use to query comma/tab separated files, IIS logs, Windows Event logs, etc. In term of outputs, we can output the result to datagrid, graph, database, text files, etc. One thing that I love about Microsoft LogParser is that we use SQL expression to query the logs (I am a bit biased on this since I am a DBA).

Microsoft LogParser is a command line utility. The installation file can be located here. There are some people that developed GUI for Microsoft LogParser. One of them can be found here.

Using Microsoft LogParser to Read SQL Error Log

SQL Error Logs in SQL Server provides a lot of information that can be use by DBA to troubleshoot issues with SQL Server. It contains server error messages, backup and restore messages, process messages, etc. We can easily view SQL Error Log using SQL Log Viewer that comes with Microsoft SQL Server Management Studio.

SQL Error Logs by default is stored in the following folder: C:\Program Files\Microsoft SQL Server\MSSQL.x\MSSQL\LOG\. It has file name of either ERRORLOG or ERRORLOG.x. They are text-based logs, and we can use Microsoft LogParser to query the logs.

To output all of SQL Error Logs into a datagrid, you can use the following command with Microsoft LogParser:

LogParser.exe "SELECT TO_TIMESTAMP(STRCAT(STRCAT(DATE,' '), TIME), 'yyyy-MM-dd hh:mm:ss') as DateTime, Source, Message FROM 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\ERRORLOG*'" -i:TSV -iSeparator:spaces -iHeaderFile:C:\SQLErrorLogHeader.txt -headerRow:OFF -nskiplines:5 -o:DATAGRID

A bit of explanation of the option used:

-i:TSV	This is to indicate that the input file used is a TSV (Tab Separated Values) file.
-iSeparator:spaces	This is to specify that we use spaces as the separator between values.
-iHeaderFile:C:\SQLErrorLogHeader.txt	The SQLErrorLogHeader.txt file can be downloaded from here. If you open one of the SQL error log, you would notice that it does not have any column header. What this option does is that it tell LogParser to use SQLErrorLogHeader.txt as the header format file. We can use the header specify in the header format file in our SQL query.
-i:headerRow:OFF	This just to tell LogParser that the first row of the input file is not header.
-nskiplines:5	With this option, we skipping the first 5 lines from the input files. If you look at one of the SQL Error log file, the first 5 lines of the error log are just the SQL Server version and build information.
-o:DATAGRID	This is to indicate that we want the output into datagrid format.

Now let's look at the SQL query that we use:

SELECT TO_TIMESTAMP(STRCAT(STRCAT(DATE,' '), TIME), 'yyyy-MM-dd hh:mm:ss') as DateTime, Source, Message FROM 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\ERRORLOG*'

Within the select clause, we use the column names specify in the SQLErrorLogHeader.txt. One quick thing to point out here is that, the TO_TIMESTAMP function is being used to convert text into datetime. Within the from clause, we just put the location of the SQL error log files. The neat thing about this is that it accepts wildcard, so LogParser can process multiple files at once.

For the SQL query, you can be as creative as you like. Some examples:

Let say that you want to find out messages in SQL Error Logs that are older than 1st January 2010, you can use the following command:

LogParser.exe "SELECT TO_TIMESTAMP(STRCAT(STRCAT(DATE,' '), TIME), 'yyyy-MM-dd hh:mm:ss') as DateTime, Source, Message FROM 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\ERRORLOG*' WHERE DateTime < TO_TIMESTAMP('01/01/2010', 'MM/dd/yyyy')" -i:TSV -iSeparator:spaces -iHeaderFile:C:\SQLErrorLogHeader.txt -headerRow:OFF -nskiplines:5 -o:DATAGRID

Or let say that you are trying to find out about database backups that was done:

LogParser.exe "SELECT TO_TIMESTAMP(STRCAT(STRCAT(DATE,' '), TIME), 'yyyy-MM-dd hh:mm:ss') as DateTime, Source, Message FROM 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\ERRORLOG*' WHERE DateTime < Message LIKE '%backup%'" -i:TSV -iSeparator:spaces -iHeaderFile:C:\SQLErrorLogHeader.txt -headerRow:OFF -nskiplines:5 -o:DATAGRID