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)

    BEGIN

        SET ROWCOUNT 5000

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

        SET ROWCOUNT = 0

        SET @intRowsToDelete = @intRowsToDelete - 5000

    END

     

    --

    -- 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)

    BACKUP LOG ePO4_%servername% WITH TRUNCATE_ONLY

    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