How to delete old call logs and other logs in vicidial or goautodial.

Step 1: SSH to the server using the Putty

Step 2:  login to mysql by typing   mysql -p

    

            (if you dont know password try below command )

            mysql -ucron -p1234

Step 3: select the asterisk database by typing

            use asterisk

step 4: Run the below command to check total disk occupied by asterisk database

            

SELECT table_schema AS "asterisk",
ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) AS "Size (MB)"
FROM information_schema.TABLES
GROUP BY table_schema;

Step 5: Run the below command to check disk space consumed by each table in asterisk database

SELECT table_name AS "Table",

ROUND(((data_length + index_length) / 1024 / 1024), 2) AS "Size (MB)"

FROM information_schema.TABLES

WHERE table_schema = "asterisk"

ORDER BY (data_length + index_length) DESC;

Step 6: as per the above output, i will be deleting the data for first top 10 tables which consumes more space

vicidial_url_log

call_log

vicidial_list

vicidial_dial_log

vicidial_admin_log

vicidial_carrier_log

vicidial_log_extended

vicidial_log

recording_log

vicidial_agent_log

Deleting each table data for the date on or before 2019 dec 23:59

Run the below command in mysql console to delete old logs ie before DEC31-2019

DELETE FROM vicidial_url_log WHERE url_date < '2019-12-31 23:59:00';

optimize table vicidial_url_log;

DELETE FROM call_log WHERE start_time < '2019-12-31 23:59:00';

optimize table call_log;

DELETE FROM vicidial_list WHERE last_local_call_time < '2019-12-31 23:59:00';

optimize table vicidial_list;

DELETE FROM vicidial_dial_log WHERE call_date < '2019-12-31 23:59:00';

optimize table vicidial_dial_log;

DELETE FROM vicidial_admin_log WHERE event_date < '2019-12-31 23:59:00';

optimize table vicidial_admin_log;

DELETE FROM vicidial_carrier_log WHERE call_date < '2019-12-31 23:59:00';

optimize table vicidial_carrier_log;

DELETE FROM vicidial_log_extended WHERE call_date < '2019-12-31 23:59:00';

optimize table vicidial_log_extended;

DELETE FROM vicidial_log WHERE call_date < '2019-12-31 23:59:00';

optimize table vicidial_log;

DELETE FROM recording_log WHERE start_time < '2019-12-31 23:59:00';

optimize table recording_log;

DELETE FROM vicidial_agent_log WHERE event_time < '2019-12-31 23:59:00';

optimize table vicidial_agent_log;

Step 7:  Reboot server once .