Deleting many events within the ePO database

Version 2

    Deleting events within ePO can be a real effort if you have to remove millions of events.

    The following script is an example I used. It deletes the events in "segments" of 5000 events each.

    The advantage is, that stopping the script will not rollback the whole process, also the transaction log will not grow extremely.


    Be sure to change %servername% to your ePO servername and that the ePO database recovery model is set to "Simple".

    When running the script via SQL Management studio set the database to your ePO database.


    WARNING! Running the script will IMMEDIATELY delete the events. Set the date/time according to your needs. No rollback option (exept restoring the DB).




    -- Delete all client events before 2011/01/01


    DECLARE @intRowsToDelete int

    SELECT @intRowsToDelete = COUNT (*) FROM EPOEvents WHERE ReveicedUTC < '2011/01/01'

    WHILE(@intRowsToDelete > 0)


        SET ROWCOUNT 5000

        DELETE FROM ePOEvents WHERE ReveicedUTC < '2011/01/01'

        SET ROWCOUNT = 0

        SET @intRowsToDelete = @intRowsToDelete - 5000




    -- Shrinking database and transaction log


    BACKUP DATABASE ePO4_%servername% TO DISK='C:\TEMP\ePO4_%servername%_backup.bak'

    DBCC SHRINKFILE ('ePO4_%servername%',10)

    DBCC SHRINKFILE ('ePO4_%servername%_log',2)


    DBCC SHRINKFILE ('ePO4_%servername%_log',2)



    Delete many events via the ePO GUI can be very slow ...



    Cheers Tom


    PS: Script enhancements are welcome as I am no SQL guru