Skip to Content

Backup catalog contains information about the backup detail (history information). As the time passes by and if proper retention period for such information is not maintained then these information keeps on growing and thus increasing the size of Backup Catalog.

The Data Backup also takes the backup of the Backup Catalog information hence it not only consuming extra space during backup but also increasing the backup time. If size increases significantly then it has significant impact on Backup Space requirement and time.

This is the reason it is always recommended to maintain a proper Backup Policy and accordingly purge (delete) the information in Backup Catalog. Suppose you have a policy of Backup Retention of 4 weeks (28 days) then any information on Backup older than 28 days in Backup Catalog is waste and has no significance. So that information needs to be deleted.

The following command will give you the detail on Backup Catalog Size and age of oldest backup history maintained in the Backup Catalog:

Select   TO_CHAR(CURRENT_TIMESTAMP, ‘YYYY/MM/DD HH24:MI:SS’) EVALUATION_TIME,   LPAD(TO_DECIMAL(C.CATALOG_SIZE_MB, 10, 2), 15) CATALOG_SIZE_MB,   TO_CHAR(B.OLDEST_BACKUP_DATE, ‘YYYY/MM/DD HH24:MI:SS’) OLDEST_BACKUP_DATE,   LPAD(B.OLDEST_BACKUP_DAYS, 18) OLDEST_BACKUP_DAYS FROM ( SELECT TOP 1     BF.BACKUP_SIZE / 1024 / 1024 CATALOG_SIZE_MB   FROM     M_BACKUP_CATALOG B,     M_BACKUP_CATALOG_FILES BF   WHERE     B.BACKUP_ID = BF.BACKUP_ID AND     BF.SOURCE_TYPE_NAME = ‘catalog’ AND     B.STATE_NAME = ‘successful’   ORDER BY     B.SYS_START_TIME DESC ) C, ( SELECT     MIN(SYS_START_TIME) OLDEST_BACKUP_DATE,     DAYS_BETWEEN(MIN(SYS_START_TIME), CURRENT_TIMESTAMP) OLDEST_BACKUP_DAYS   FROM     M_BACKUP_CATALOG ) B

/wp-content/uploads/2016/08/1_1022170.jpg

If you find information in Backup Catalog which is not in sync with your Backup policy then execute the following SQL command in HANA studio to prepare command for Catalog Deletion:

Select

MAP(BI.MIN_RETAINED_BACKUPS, -1, ‘no restriction’, LPAD(TO_CHAR(BI.MIN_RETAINED_BACKUPS), 20)) MIN_RETAINED_BACKUPS,   MAP(BI.MIN_RETENTION_DAYS, -1, ‘no restriction’, LPAD(TO_CHAR(BI.MIN_RETENTION_DAYS), 18)) MIN_RETENTION_DAYS,   LPAD(MAX(B.BACKUP_ID), 15) BACKUP_ID,   TO_CHAR(MAX(B.SYS_START_TIME), ‘YYYY/MM/DD HH24:MI:SS’) BACKUP_TIME,   ‘BACKUP CATALOG DELETE ALL BEFORE BACKUP_ID’ || CHAR(32) || MAX(B.BACKUP_ID) || CHAR(32) || BI.COMMAND_OPTIONS COMMAND

FROM

( SELECT                  /* Modification section */     28 MIN_RETAINED_BACKUPS,     28 MIN_RETENTION_DAYS,     ” COMMAND_OPTIONS             /* empty string, ‘WITH FILE’, ‘WITH BACKINT’ OR ‘COMPLETE’ */   FROM     DUMMY ) BI, ( SELECT     BACKUP_ID,     SYS_START_TIME,     ROW_NUMBER() OVER (ORDER BY BACKUP_ID DESC) ROW_NUM   FROM     M_BACKUP_CATALOG   WHERE     STATE_NAME = ‘successful’ AND     ENTRY_TYPE_NAME IN ( ‘complete data backup’, ‘data snapshot’) ) B WHERE   ( BI.MIN_RETAINED_BACKUPS = -1 OR B.ROW_NUM > BI.MIN_RETAINED_BACKUPS ) AND   DAYS_BETWEEN(B.SYS_START_TIME, CURRENT_TIMESTAMP) > BI.MIN_RETENTION_DAYS GROUP BY   BI.MIN_RETAINED_BACKUPS,   BI.MIN_RETENTION_DAYS,   BI.COMMAND_OPTIONS

—————————————————————————————————————————————————————————————————-

– MIN_RETAINED_BACKUPS   Minimum number of retained backups (i.e. backup catalog entries are only deleted if they are older than   the defined number of most recent data backups)

                              28               –> Only delete backup catalog entries older than the 28 most recent data backups

                              -1                –> No restriction related to existing data backups

– MIN_RETENTION_DAYS   Minimum threshold of retention days (i.e. backup catalog entries are only deleted if they are older than   the defined number of retention days)

                              28              –> Only delete backup catalog entries if they are older than 28 days

                              -1               –> No restriction related to retention days

—————————————————————————————————————————————————————————————————–


Note: Please look into the value for MIN_RETAINED_BACKUPS and MIN_RETENTION_DAYS for which the value is maintained as 28 days which is in Sync with Backup retention period of 4 week in my organization. This needs to be adjusted as per your organizational backup retention policy.

The output of the above SQL command would be as follows:

/wp-content/uploads/2016/08/2_1022186.jpg

Select the command given in the last column and then execute it through SQL prompt in HANA studio:

BACKUP CATALOG DELETE ALL BEFORE BACKUP_ID 1469498401561 COMPLETE

/wp-content/uploads/2016/08/3_1022188.jpg

The format of the command is as follows:

BACKUP CATALOG DELETE ALL BEFORE BACKUP_ID <data_backup_id>  [WITH FILE | WITH BACKINT | COMPLETE]

After successful execution of the command please re-verify the detail in the Backup Catalog by executing the first SQL command given at the start of the document executed on view M_BACKUP_CATALOG. The output would be something like below:

/wp-content/uploads/2016/08/4_1022196.jpg

Here you can see the Oldest Backup Days reduced from 32 to 29 now and the space consumed by Backup Catalog has been reduced from 4.5MB to 4.24MB. This is just an example where space reduction is not that high and reduction in only 0.26MB, but in poorly systems this value would be quite high and once the process given in this document followed then it has significant impact on Disk/space and backup timing.

Recommendation is to do this activity twice a month or monthly.

To report this post you need to login first.

Be the first to leave a comment

You must be Logged on to comment or reply to a post.

Leave a Reply