Entries in MSDB database

  • I have configured a log backup and restore schedule (using Logshipping) for a small database on 2 machines to run every 2 minutes. I think MSDB database got full because of this. So i disabled the jobs and i deleted the database. But still i see this database related entries in msdb tables like backupset, restorehistory, backupfiles etc.. Why ?

    Can i manually delete these rows ?

  • Check out sp_Delete_backuphistory

    http://technet.microsoft.com/en-us/library/ms188328(SQL.90).aspx

    Be careful with this SP as it's slow as hell. If you have like 6 months worth of history, delete out like a few weeks at a time. Run it in small pieces so you can see how nasty it is.

    it basically works by passing a date, and anything older than that date is deleted. So to delete anything older than 60 days, you could run this:

    declare @days datetime

    set @days = getdate() - 60

    exec sp_delete_backuphistory @days

  • But when i delete the database, i guess the action should delete all related entries in msdb as well. Is there any logical reason behind non-deletion ? Otherwise, msdb would grow on continuously ? How to tackle this ?

  • No, it shouldn't delete this stuff. The reason is that you might drop a database (there is no DELETE) because of an issue and then restore it. The information in msdb helps with that.

    MSDB doesn't grow uncontrollably. There are settings to trip back history in msdb.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply