Note: This article is only useful for self-hosted installations of LiveAgent, as it requires database access.
When you need to reduce the size of database (e.g. you're running out of disk space) the first thing you should consider is use our Amazon S3 File Archive plugin.
If you don't want to or for some reason can't use Amazon S3 File Archive plugin, the second option you can do is remove some of the old and no longer needed attachments. One of the helpful tools is our Ticket attachments plugin. The plugin can be activated in Configuration->System->Plugins section of your LiveAgent panel. This plugin lists all attachments in current ticket, and can be configured to allow deletion of the files. However going through all your tickets one by one and looking for the ones with most attachments would be very time consuming.
Instead of that, you can use this SQL command to show you the tickets with attachments that consume the most space.
select c.code, c.datecreated, SUM(f.filesize) as attachments_size from qu_g_file_links fl join qu_la_messages m on fl.entity_id = m.messageid join qu_g_files f on fl.fileid = f.fileid join qu_la_conversations c on m.conversationid = c.conversationid where fl.rtype='M' and fl.is_included_image = 'N' and f.filesize is not null group by m.conversationid order by attachments_size desc
You can then use value from the code column to look up the ticket, and using Ticket Attachments Management plugin delete the files (deletion must be enabled in plugin configuration).
Files are stored in database deduplicated. That means if you upload the same attachment to more tickets, the file data is stored only once and each of the tickets references the same file. The file is preserved while any reference to the file exists. Using Ticket attachments plugin to delete attachments from tickets only removes the reference, so unless you delete the last reference to a file, the actual file data will not be deleted. Files which no longer have any references are deleted by a background task executed weekly.
It is possible to force the background task to execute daily by executing this SQL command (recurrencepresetid value 'A' means daily, 'B' means weekly)
UPDATE qu_g_plannedtasks SET recurrencepresetid='A' WHERE classname='Gpf_Tasks_DeleteUnusedFiles'
After the background task has run and the file data has been deleted from the table, to actually shrink the database file storage it may be necessary to execute the following SQL command. Be aware that this command can take a very long time.
OPTIMIZE TABLE `qu_g_filecontents`