Problems with GIS/SI index/backup/purge services

One of our clients had some problems with the Backup Service in their GIS system. They turned it off for about 2 months and at the end of this period there were about 4M records with ARCHIVE_FLAG = 0 in the ARCHIVE_INFO table. The DB size had increased to hundreds of GBs. I decided to change all of the records with ARCHIVE_FLAG = 0 to ARCHIVE_FLAG = 2 and ARCHIVE_DATE = “now() + 1 year” to make them “invisible” for both the Backup and Purge Services. Then I turned the Backup Service back (thus I stopped the DB growing) and started “returning” those changed records back day by day. So, the index/backup/purge services got back to the rut.

Important: it’s just an idea, not the direct instructions. You should understand how it works to make such changes… If you don’t understand the SI/GIS DB you mustn’t do anything with it except maybe select queries.

Here is the SQL query I used (I ran it several times, day by day to avoid excessive DB loading):

where TO_CHAR(ARCHIVE_DATE, 'yyyy-mm-dd')  = '2014-01-01' and ARCHIVE_FLAG = 0;

Gennady Kim


One thought on “Problems with GIS/SI index/backup/purge services

  1. Pingback: Sterling GIS/SI: problems with the disk space | EDI shortcuts

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s