Skip to content

Netezza – Backups status showing active

ISSUE:
When you check the database backup history using below query for your Netezza you may find some active backups.

select * from _V_Backup_History

CAUSE:
Now when you try to check active conenctions, you are not able to find any backup running. Here you may also see the start time being too old. So why is system table/view showing us wrong information? Reason for this wrong information is that if you kill any backup process directly from OS by issuing Kill Linux process id, then Netezza is not able to understand if backup is really killed and mark it as failed or if backup is still running. So, system table is not updated and you get wrong information.

RESOLUTION:
In order to fix this you may have to directly update the system table _T_Backup_History which holds this information.

Below query will return list of all active backups

select * from _V_Backup_History where status = 'ACTIVE'

Below query will return the output as total count of active, completed or failed backups

select distinct status, count(*) from _T_Backup_History group by status

Not, Here is the decoding for status field in _T_Backup_History table.
0 = Active
1 = Completed
2 = Failed

RUn below query to update the system table and mark the actually failed backups (still showing active in system table) to failed.

update _T_Backup_History
set Status = 2 where status=0

ADDITIONAL USEFUL INFO:
Query to find active backups in Netezza

Select DBNAME, STATUS,USERNAME,STARTTIME, LASTUPDATE from _V_Backup_History
where DBNAME != 'NULL' and status = 'ACTIVE'

Here we have put DBNAME not equal to NULL because if a database is dropped from Netezza then it updates the backup tale and replace the database name with NULL.