The table for attachments ( sys_attachment_doc) doesn't decrease in size after atth is deleted.

[Dmitry Tovb]

We have a process that deletes the attachment files from the table but the table doesn't decrease in size. What needs to be done to decrease the attachment table size after deletion?

Steps to reproduce:
- Create new issues with large attachments.
- Get database size report
- Delete attachments
- Get the database size report again - the table size doesn't decrease.

Thank you! 

[Robbie · Accepted solution]

Hi @Dmitry Tovb,

From what I understand, table optimization to deallocate storage is not automated.

Whilst table cleaner (auto flush) jobs are available, I believe the deallocation is based on size thresholds. I'd also be hesitant to run these database jobs on Production, especially during peak usage.

ServiceNow Support should be able to both advise and implement the change. Raise a case with SN Support. 

To help others (or for me to help you more directly), please mark this response correct by clicking on Accept as Solution and/or Helpful.

Thanks, Robbie

[Maik Skoddow · Accepted solution]

Hi @Dmitry Tovb 

When rows are deleted from an DB table, the rows are simply marked as deleted and not physically deleted. The free space is not returned to the operating system for re-use automatically. Instead any optimization jobs have to be run.

Basically, if you delete more than 50% of the data in the table at a time that should trigger an automatic drop/swap and reclaim space, but on large tables you may only be targeting 5-10% of data to delete at any given time (based on your retention policies and such), so that might not apply. You will need to ask ServiceNow to rebuild tables to reclaim memory in the event you delete any data, particularly on large tables. 

Maik

[Robbie · Accepted solution]

Hi @Dmitry Tovb,

From what I understand, table optimization to deallocate storage is not automated.

Whilst table cleaner (auto flush) jobs are available, I believe the deallocation is based on size thresholds. I'd also be hesitant to run these database jobs on Production, especially during peak usage.

ServiceNow Support should be able to both advise and implement the change. Raise a case with SN Support. 

To help others (or for me to help you more directly), please mark this response correct by clicking on Accept as Solution and/or Helpful.

Thanks, Robbie

[Maik Skoddow · Accepted solution]

Hi @Dmitry Tovb 

When rows are deleted from an DB table, the rows are simply marked as deleted and not physically deleted. The free space is not returned to the operating system for re-use automatically. Instead any optimization jobs have to be run.

Basically, if you delete more than 50% of the data in the table at a time that should trigger an automatic drop/swap and reclaim space, but on large tables you may only be targeting 5-10% of data to delete at any given time (based on your retention policies and such), so that might not apply. You will need to ask ServiceNow to rebuild tables to reclaim memory in the event you delete any data, particularly on large tables. 

Maik

[Mark Roethof · Accepted solution]

Hi there,

Its indeed like Maik is mentioning, ServiceNow can rebuild the table for you. You might follow my current running blog series on Keeping your instance tidy which covers also this.

- Maintain Audit Delete and Audit Relation
- Maintain Attachments and Attachment Documents
- Activating/adding Table cleaners
- Reduce (over)auditing
- Lower duration Table Rotation
- Drop syslog_trans_prejakarta* tables
- Cleanup Shadow tables 

Copy/paste from one of the blogs:
"Optimize table

After having the Attachment table and the Attachment Docs table cleaned up, you do need to have at least the Attachment Docs table optimized. While you can do so yourself, for larger tables it is saver to involve ServiceNow Support. Optimizing large tables yourself can take several hours and can lock the specific table. ServiceNow Support can do this from the backend, and would likely opt to rebuild the tables as that would avoid locking them."

[Dmitry Tovb]

Thank you for the answers.  I have opened the case with ServiceNow and they confirmed that the platform will automatically rebuild the table when we delete 50% of the data.