Overview

You would like to clean up stale lists that have not been used for some period of time so that they can be removed in order to save space in the database.

Prerequisites

• You have enabled the log_ under Utilities > Administration > Server > Server Settings > Automatic Maintenance, Logging tab by selecting Yes, log all messages that ListManager sends to mailing lists.

Solution

The following query may be used to generate a list of lists that have not been used since a specific date. 

The query can be modified to provide additional details such as, the List owner (Administrator) by adding Admin_ or other fields to the query. For more information check the lists_ reference page.

1. Log on to Lyris LM as an Administrator
   
   
2. Navigate to Utilities > Administration > Server > Database > View and Edit Data > Run Your Own SQL Query
   
   
3. Copy and paste the SQL query below into the query field after replacing YYYY-MM-DD with the date you want to search from
   

   select distinct list_,site_, max(created_) oldestmail from log_
   where list_ in (select name_ from lists_) AND created_ < 'YYYY-MM-DD'
   group by list_,site_ order by oldestmail asc

If successful, you will be taken to the View Query Results page; if unsuccessful, you will see the SQL error generated by your SQL server.