SQL 2005 errorlog with LogParser

DBAs/sysadmins/other folks responsible for SQL installations should check the errorlog of the SQL servers if something happens to the servers and if nothing happens, then they still have to check the logs regularly as sometimes things happen which are not so obvious at first (e.g. someone is trying to figure out your sa password – unsuccessfully so far, but tomorrow they may succeed). But it’s definitely a burden, especially if you need to do the checks manually, therefore skipping this task is very persuasive. If you’re tough and techie enough then you may decide to set up an automated log collection for your SQL 2005 servers with some pre-filtering, eventually, you’re not a robot, but you can create a software one.

The perfect tool for the task is the LogParser, which is what its name suggests: a tool designed for reading literally any kind of text logs – and a bit more (e.g. Windows eventlog). It knows dozens of formats by heart and you can teach it any format using a simple format file. You can use a SQL-like syntax to filter records and redirect the output into another format (text or SQL) if you want. I used this tool to filter my SQL 2000 errorlogs and mail them to myself on a daily basis. However, when I implemented the regular task with the incremental file read, I got nothing as an output. if I omitted the parameter -iCheckpoint, everything was fine. Sounded weird and Google didn’t help on this. Finally, I figured out what the difference was between the SQL 2000 and the SQL 2005 errorlogs: the SQL Server 2005 creates a unicode file while the SQL 2000 produced an ANSI Latin1. Fortunately, the LogParser has a parameter which tells the encoding of the file: I added -iCodepage:-1 to the command-line, and everything went fine. Finally, here’s the cmd file for collecting SQL errorlogs:

del SQLerrorlog.log
LogParser "select 'SQL2000' as ServerName, STRCAT(STRCAT(DATE,' '), TIME) as LogDateTime, SOURCE, Message INTO reports\SQL1.txt From '\\SQL1\SQLLogs\ERRORLOG' WHERE SOURCE <> 'backup'" -i:TSV -iSeparator:spaces -iHeaderFile:formatfile.txt -headerRow:OFF -nskiplines:9 -iCheckPoint:SQL1.cpt
LogParser "select 'SQL2005' as ServerName, STRCAT(STRCAT(DATE,' '), TIME) as LogDateTime, SOURCE, Message INTO reports\SQL2.txt From '\\SQL2\SQLLogs\ERRORLOG'" -i:TSV -iSeparator:spaces -iHeaderFile:formatfile.txt -iCodepage:-1 -headerRow:OFF -nskiplines:9 -iCheckPoint:SQL2.cpt
copy reports\*.txt SQLerrorlog.log

The format file, called here formatfile.txt, is quite simple:

DATE TIME SOURCE Message

Here you go. Adjust the filters and read the results daily. I hope you’ll find no problems in them :)

Leave a comment