Return
-
Title
How to manually purge data from the Active Administrator database -
Description
Is there a way to purge data from the database without using the AA archive/purge tasks? -
Resolution
It is highly recommended to back up your database prior to purging the data as the purge operation cannot be reversed. Use SQL Server Management Studio to run the queries below.This query will return the date of the oldest record in the DB:select Min(TimeGenerated) from tblalertlogThen run the following to show how many records older than 60 days exist:SELECT COUNT(*)FROM tblalertlogwhere DateDiff("d", Convert(nchar(15),TimeGenerated,101), Getdate()) > 60The following will delete all records greater than 60 days, however it may best to perform the operation in chunks, starting with a greater number of days based on the oldest record date returned above. The reason is that the purge operation utilizes the tempdb on the SQL server, causing it to grow temporarily as it deletes records. Purging a large number at once could result in consuming a lot of diskspace:Delete from tblalertlogwhere DateDiff("d", Convert(nchar(15),TimeGenerated,101), Getdate()) > 60Once data has been purged from the database you may want to use SQL Server Management Studio to shrink the database and recover the white space as free disk space.