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.
- SAP Managed Tags:
5 Comments
