How do I ensure no duplicates when using LogParser to load IIS logs into SQL Server every fifteen minutes?

Question

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:

SELECT *
FROM \\Path\To\Logs\ex*.log
WHERE date = SYSTEM_DATE()
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?

Answer 1

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

Answer 2

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:

SELECT *
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:

SELECT *
FROM \\Path\To\Logs\ex110224.log

Answer 3

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.