Zabbix Database Cleanup – Delete Old Data

Few posts on the internet about this but quickest way i found of deleting old data below.

Save the below code as .sql and run from the server with…

#mysql zabbix -p ./deleteolddata.sql

SQL CODE –

-- intervals in days
 SET @history_interval = 7;
 SET @trends_interval = 90;

DELETE FROM alerts WHERE (UNIX_TIMESTAMP(NOW()) - clock) > (@history_interval * 24 * 60 * 60);
 DELETE FROM acknowledges WHERE (UNIX_TIMESTAMP(NOW()) - clock) > (@history_interval * 24 * 60 * 60);
 DELETE FROM events WHERE (UNIX_TIMESTAMP(NOW()) - clock) > (@history_interval * 24 * 60 * 60);

DELETE FROM history WHERE (UNIX_TIMESTAMP(NOW()) - clock) > (@history_interval * 24 * 60 * 60);
 DELETE FROM history_uint WHERE (UNIX_TIMESTAMP(NOW()) - clock) > (@history_interval * 24 * 60 * 60);
 DELETE FROM history_str WHERE (UNIX_TIMESTAMP(NOW()) - clock) > (@history_interval * 24 * 60 * 60);
 DELETE FROM history_text WHERE (UNIX_TIMESTAMP(NOW()) - clock) > (@history_interval * 24 * 60 * 60);
 DELETE FROM history_log WHERE (UNIX_TIMESTAMP(NOW()) - clock) > (@history_interval * 24 * 60 * 60);

DELETE FROM trends WHERE (UNIX_TIMESTAMP(NOW()) - clock) > (@trends_interval * 24 * 60 * 60);
 DELETE FROM trends_uint WHERE (UNIX_TIMESTAMP(NOW()) - clock) > (@trends_interval * 24 * 60 * 60);

 

You can change the bit at the top to cut down on the history kept or trends data.

 

Happy tidying.

5 thoughts on “Zabbix Database Cleanup – Delete Old Data

  • query for Zabbix+postgresql backend
    DELETE from history where (extract(epoch from now() at time zone ‘utc’) * 1000)::bigint – clock > (7 * 24 * 60 * 60 * 1000);

    then i’d like to do full vacuumdb –full to free up space.

  • Hello,
    When i tried to execute DELETE FROM trends_uint WHERE (UNIX_TIMESTAMP(NOW()) – clock) > (@trends_interval * 24 * 60 * 60); command, always receive the following error message:

    Failed to execute SQL : SQL DELETE FROM trends_uint WHERE (UNIX_TIMESTAMP(NOW()) – clock) > (@trends_interval * 24 * 60 * 60); failed : Lost connection to MySQL server during query

    I tried to restart MySql and the Server, but i obtain the same error. Why is this happening?

    Regards.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.