DB Maintenance - Servicenow best practice

[PaulSylo]

What are the best practices followed on Database size reduction apart form having any rentention policy? what are the ables need to be cleaned up on Monthly basis to maintain the DB size within the limit. I have DB size 5 TB but it almost came upto 4.75 TB. Sys_attachment_doc is more than 2 TB. Can advise on Best practices? 

[Filipe Cruz]

Hi Paul,

My recommendation is to go to support.service-now.com and "order" the database footprint item to see the top 10 tables that consume more storage.
You already identified the sys_Attachment_doc table. 

You need now to perform queries to your system to understand if those sys_attachment and sys_attachment_doc records are orphans, meaning that you need to see if the record associated with the attachments has been delete from your system.

If so, you can delete those attachments.

I did this in a customer instance that had 10 TB 🙂

Also, do you have archive enabled? if so we can talk a little bit more! 

Please, order that database footprint and share here the results.

Hope this helps!

Please, don't forget to mark my answer as correct if it solves your issue or mark it as helpful if it is relevant for you!

Best Regards,

Filipe Cruz

[PaulSylo]

Thanks a ton, Felipe. I got the top 10 tables from ServiceNow. As you mentioned sys_attachment is almost 2 TB. can you tell me how you did this,

"You need now to perform queries to your system to understand if those sys_attachment and sys_attachment_doc records are orphans, meaning that you need to see if the record associated with the attachments has been deleted from your system"

[Filipe Cruz]

Hi Paul,

Sure I can!

So, every attachment is connected to a specific record based on the table name and table sys id fields. 
To ease my explanation, let's consider an incident as the main record.

If you delete an incident the cascade delete rules will delete the sys_attachment automatically. But if you are doing the deletion with table cleaners or using the archive engine, you'll need to do that deletion by yourself.

Since there is no reference in the attachment to the original record (as I mentioned before, the linkage is done via table name and table sys id) you need to query the system to check if the record associated with the attachment still exists. You can use a code similar to this one:

checkOrphanSA();

function checkOrphanSA(){
  var gr = new GlideRecord("sys_attachment");
  gr.addEncodedQuery(<filter query to pick the attachments you want to analyze");
  gr.setLimit(1000);
  gr.query();
  while(gr.next()){
  
     var gr2 = new GlideRecod(gr.table_name);
     gr2.get(gr.table_sys_id);

     if(!gr2.isValidRecord()){
        //this means that the parent record does not exist! The sys_Attachment should be deleted!
       var gr3 = new GlideRecord("sys_attachment_doc");
       gr3.addQuery("sys_attachment", gr.sys_id);
       gr3.query();
       while(gr3.next()){ 
         gr3.deleteRecord(); //delete sys_attachment_doc recird
       }

       gr2.deleteRecord(); //deletes orphan sys_attachment record

     }

  }

}

You should do this in chunks of 1000 records in order for you to control the timings.

Then you should do the same for sys_attachment_doc.
In specific conditions, when you delete the sys_attachment the sys_attachment_doc records continue to exist.

You can use this script for that:

cleanAttachments();
function cleanAttachments(){
	var gr = new GlideRecord("sys_attachment_doc");
	gr.addEncodedQuery("sys_attachment.table_nameISEMPTY");
	//gr.setLimit(1000);
	gr.query();
	while(gr.next()){
		gr.deleteRecord();
	}	
	
}

Use this in fix scripts.

Test out the first script and let me know the result.

Hope this helps!

Please, don't forget to mark my answer as correct if it solves your issue or mark it as helpful if it is relevant for you!

Best Regards,

Filipe Cruz

[PaulSylo]

Sure, thank you so much. I will work on this Test instance and let you know. post our testing i will create a document on the same !.

Regards,

Paul