Event Log Table Querying really slow with large tables

[JonG]

Is there anything that can be done with indexing to speed up the querying of the event logs that are extracted. The event sections of the Lansweeper configuration tool and the reports are unusable with large tables. I assume it is summarising event id data but not sure if there are any indexes to support it. The server CPU more or less maxes out and eventually comes back with some results but I now have over a 2 million records in the event table and the experience is not great. Any machine that has it's events polled takes an age to load up the summary. I have over 4000 computers in the system now and overall it is ok other than the event log data.

I have actually reduced the amount of events my domain controllers are logging by applying a revised GPO to them. Is there a command I could use to at least empty the current event log tables and start again ?

Thanks

Jon

[Hemoco]

JonG wrote:
Is there a command I could use to at least empty the current event log tables and start again ?

Please do the following:
• Stop the Lansweeper service.
• Execute the command below in SQL Server Management Studio. This will delete all event log information from your database.

use lansweeperdb
GO
truncate table tblntlogevent

• Right-click on the lansweeperdb database in SQL Server Management Studio and choose Tasks\Shrink. Shrink both the database and files.
• Restart the Lansweeper service.


We would also recommend the following:
• In your Lansweeper configuration console under Scanning Servers\Your Server\Options\Eventlog Scanning, disable any event types you don’t need. Scanning non-error events can quickly increase the size of your database.
• In your Lansweeper configuration console under Scanning Servers\Your Server\Options\Scanning & Cleanup Options, lower the setting below as much as possible.

Delete eventlog entries after X days.