I would like to set up a scheduled task to run every fifteen minutes on a work web server that will run LogParser on today's IIS log file and insert it into a SQL Server database table.

How would I ensure that I don't copy in any duplicate data but at the same time ensure that all the records have been copied?

Also how would I get LogParser to always look at todays log file without running expensive queries such as SELECT * FROM ex*.log and using a date and time condition?

What I have been playing with so far is:

FROM \\Path\To\Logs\ex*.log
AND time > SUB(SYSTEM_TIME(), TO_TIMESTAMP('00:30', 'hh:mm'))

However if I ran this every half an hour I'm sure to get duplicate entries. Also if it didn't work for whatever reason I would end up with missing data which I would eliminate by just overwriting the whole file for the previous day each morning.

Any tips?


Have you checked the ' -iCheckPoint' switch? It stores a timestamp of the last run and only accesses subsequent records.

| improve this answer | |
  • That's too easy. – Skyhawk Dec 17 '11 at 4:13

After some playing around I can actually answer part of my own question.

The code to be able to look at just today's IIS log is:

FROM \\Path\To\Logs\ex%date:~8,2%%date:~3,2%%date:~0,2%.log

I'm not sure if that works for dates that are not UK standard but this works for me. The code above generates this for today's date which is 24/02/2011:

FROM \\Path\To\Logs\ex110224.log
| improve this answer | |

I overcame this problem by simply creating a PRIMARY KEY on RecordNumber column in SQL server table, which helped to stop duplicates.

Also, in a clustered environment, I overcame this by creating a composite PRIMARY KEY on (ComputerName,RecordNumber) and this worked awesome, as it was always a unique combination in my environment.

When intentionally tested a duplicate log parsing in LOG PARSER, it showed up the expected error of 'primary key violation' on LOG PARSER screen itself....and issue resolved.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.