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