0

Is it possible to run something like dbo.sp_delete_backuphistory to only delete transaction log backups without rolling your own scripts? Using Ola's maintenance scripts, we do 5 minute log backups on all our critical databases and nightly differential and weekly full backups. This means we get about 8500 transaction log backups in a month, but only 30ish differentials and 4-5 full backups.

We keep the full and differentials for much longer than we do log backups, so we'd like to keep the backup history for those, but we'd like to reduce the size of our backup tables (particularly backupset) as it's slowing down our monitoring tool.

  • SELECT OBJECT_DEFINITION(OBJECT_ID('msdb.dbo.sp_delete_backuphistory')) shows that the sp is really just a bunch of deletes from tables in msdb. Can you use that as a starting point? – Forrest Nov 7 '16 at 22:19
  • yep, we could role our own. Was hoping there might be something already built we could use – Greg Nov 7 '16 at 22:28
1

There isn't any builtin code to just delete the transaction log backup history. You'd have to roll your own to achieve that.

But I would recommend that you just dump the data that you want to keep in a separate table and purge msdb backup history normally with sp_delete_backuphistory.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.