Best Way to Handle Archive Logs

[Anurag Tripathi]

I have recently introduced archiving on my instance. The Archival logs are increasing hugely and will soon need to be addressed. i am sure i am not the only one who has come across this so i just want to see what others have done to address this.

[sergiu_panaite]

There is no benefit if you put archive log on extension. I wouldn't do that. There is a difference between sys_email/sys_audit and sys_archive_log. Email and Audit are tables that we access very frequently so they benefit from extension, but this is not the same for archive log. Plus extension is not reducing the size of the table.

What you need for that table to be able to access it faster is proper indexes. By default when you use navigator link to go to Archive log it uses a filter condition on "Created on this week", therefore you need an index on "sys_created_on". Then depending on other ways of accessing records in this table, you might need other indexes, like filtering on "Restored" would require an index on that field as well.

The field that makes archive log table big is "payload", so that's one field I would not create an index on it, as it's not that useful.

Hope this helps. If you have other questions let me know.

[Anurag Tripathi]

Thanks Sergiu,

This was surely helpful.

Chuck, thank you too to spare time for this.

[Chuck Tomasi]

You are welcome. And thank you for the good discussions and learning opportunity. Hopefully you got your questions answered. It's up to you to choose which, if any, of the comments is "correct".

[Jon Barnes]

Also, you will need to consider that setting up archiving causes your overall database space to grow. This is because not only are you keeping the record in the archived version of the table, but in the archive log, you are keeping the payload of the original record in xml format. That is why this table is going to grow huge. My suggestion to you is to work on getting another lower tier of storage setup on your DB server, and move some of the large tables that don't require super fast access over to them. This is a lot of work, and probably outside the box of what servicenow would recommend, but you may have to do it, because of the vast size of your instance + the fact that your client may not want to pay for that much SSD disk space.

So the alternative is to buy spinning disks (Which is cheaper), and working with the DBAs to get some of those tables moved over. This is quite a large task and requires alot of planning, and testing, but it could be much more cost effective in the long run.   Ultimately, it is up to you though.

Also, I believe there is an agreement to keep so many months of email data. These tables are huge because the keep the entire payload of every email sent or received. If you have it on extension, you may have to work with your DBAs to purge some of the older shards. For example, you may only be required to keep the last 3 months of emails, so purge the older ones to free up space.