Unable to truncate audit_main table

Document ID : KB000100259
Last Modified Date : 06/06/2018
Show Technical Document Details
Issue:
Our non-prod Layer7 was unable to process any request and our found the audit log threshold of 90% was reached.

We tried to truncate the tables manually but we are unable to truncate audit_main table. It takes very long time and we are unable to determine whether anything is happening at all.

We can truncate other tables which brought down the size and our system is working again now.

But soon we will again face this issue once the threshold again reach 90%. Please let us know on what can be done to truncate audit_main table successfully. Thank you. Regards, 
Resolution:
Table truncate is usually the quick way to delete data - but yes for large datasets the truncate can take a long time. 

It can also lock if there are other processes active as well. 

------ 
1) Perhaps an alternartive would be to use the delete statement with a limit clause; 

delete from audit_main limit 10000; 

or : 

delete from audit_main order by time limit 10000; 

http://www.mysqltutorial.org/mysql-delete-statement.aspx 

------ 
2) Alteratively you could rename the table and delete it: 

RENAME TABLE table_name TO t1; 

CREATE TABLE table_name LIKE t1; 

DROP TABLE t1; 

https://stackoverflow.com/questions/17762361/truncate-table-taking-very-long-time-is-it-normal