DB12 - Backup History - Memory low - leave transaction before taking a break!

[ken_halvorsen2]

Has anyone else had a problem with the increasing long runtime to get the results after you click on "Backup History" in Transaction DB12? Better yet has anyone solved this problem?

Lately I'm taking a full 20 seconds before the Backup History is returned, along with a warning about the Memory low! Leave transaction before taking a Break!

We run 1 - Weekly SQL Full, the other days are Differentials, with Transaction log backups running every 5 minutes.

Does anyone have any suggestions to fix this?

Thanks

Ken H

[Former Member · Accepted solution]

You need to use stored procedure "sp_delete_backuphistory".

Please refer to BOL for syntax.

[ken_halvorsen2 · Accepted solution]

Thanks Ratnajit and Franco

I had run this earlier but had left 2 years worth of history. But I still had the problem. So I deleted everything up to 4 months old. Still seems a little slow though, maybe I need to run the stats before testing.

To delete the history I used:

sp_delete_backuphistory @oldest_date = '01/01/2012'

To find out how many rows you have I used:

select count(*) from backupset with (nolock) where backup_start_date < '08/08/2012'

[former_member189725]

This seems the work process which is executing the query for fetching the backup history has gone to PRIV mode meaning the work process has started using the heap memory . Suggest you to cleanup the old logs and also run the cleanup periodically which are no more required .

Regards

Ratnajit