Large Backup Catalog Size and Process to Maintain it

[Former Member]

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

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:

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

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:

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.

[HEROSTK]

Hi Sheo, great information.

I was testing the query, and there is an error on this part.

( SELECT   /* Modification section */     28 MIN_RETAINED_BACKUPS,     28 MIN_RETENTION_DAYS,     ” COMMAND_OPTIONS

The character " where end ?

 

Regards

 

[SyedShoeb]

Thanks Sheo. Correct syntax of the command is

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'

[akash_miskin]

Hello Syed,

 

Do we need to execute the second command for every backup catalog or for the latest backup catalog. ?

One more thing is that i am unable to get any output for the second command.

Kindly suggest next step.

[former_member618828]

Hello Sheo,

Great information.

we are facing similar issue in our landscape where catalog size was 3.5 GB. we have tried to delete older backup id's but still there was no change in Catalog size and old data backups.

i have followed your Sql query to resolve our issue but there was no output displayed for 2 select syntax.

 

Query used :

 

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 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'

output:

;MAP(MIN_RETAINED_BACKUPS,-1,'no restriction',LPAD(TO_CHAR(MIN_RETAINED_BACKUPS),20));MIN_RETAINED_BACKUPS;MIN_RETENTION_DAYS;BACKUP_ID;BACKUP_TIME;COMMAND

 

could you please suggest Query which went wrong here.

 

Thanks in advance

Regards,

Sravikrishna

 

[former_member623328]

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'