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

Fixing User Login Mapping

You might have been in a situation where you have been asked to sync up the development database with production database. Fair enough. So you started to backup your production database and restore that database in the development environment. You thought to yourself, “That was easy.” But then after an hour has past, one of the developer went to you and said that now he can not access the database that you’ve just restored in the development environment. Doh!

What’s Wrong

For SQL Server authentication logins, SQL Server would assign security identifier (SID). SQL Server would store the login information in the master database. Each database would also store information on who can access the database. They are linked together by the user SID. Since you were only restoring the user database, it is very likely that the SID of the users stored in the master database (the one that grants login to the SQL Server) do not match with the SID of the users stored in the user database that you’ve just restored (the one that grants access to the user database). Thus, we have a situation where we have orphan users.

Detecting Orphan Users

To quickly detect orphan users for a particular user database, you can use the following script (in this example assume that AdventureWorks is the user database):

USE [AdventureWorks]
GO
EXEC sp_change_users_login ‘Report’
GO

Alternatively, you can also use the following script:

USE [AdventureWorks]
GO
SELECT [name], [sid]
FROM sysusers
WHERE name not in (‘guest’, ‘dbo’)
AND sid IS NOT NULL
AND issqluser = 1

Fixing Orphan Users

Now let say that when we run the script above, we see that user ‘Developer1’ is orphan. Assuming that in the development SQL Server, we already have login for ‘Developer1’, to fix this, we can run the following script:

USE [AdventureWorks]
GO
EXEC sp_change_users_login ‘Auto_Fix’, ‘Developer1’
GO

Alternatively, we can also run the following script:

USE [AdventureWorks]
GO
ALTER USER Developer1
  WITH LOGIN = Developer1
GO