2

while running the following query:

-- DATEADD (datepart , number , date )  

DECLARE @dt DATETIME
SELECT @dt = DATEADD(month,-6,getdate())
select @dt
EXEC msdb.dbo.sp_delete_backuphistory @oldest_date = @dt 

I get lots of locks and blocks, possibly because this command has not been run for a while, if ever.

enter image description here

Is there a way to find out how much is there to delete in each of the involved tables, before I actually run or plan to run this command?

I use dateadd to calculate 6 months.

Within sp_delete_backuphistory the following tables are trimmed:

sp_delete_backuphistory must be run from the msdb database and affects the following tables:

backupfile

backupfilegroup

backupmediafamily

backupmediaset

backupset

restorefile

restorefilegroup

restorehistory

  • 2
    The msdb database doesn't have indexes on the system tables, so if you had many backup/restore operations over time, purging records might take long. I don't know how to see the records to delete beforehand, but you can try creating indexes as suggested in the following post and doing your purge in batches (with very old dates first, then closer to last 6 months). weblogs.sqlteam.com/geoffh/2008/01/21/msdb-performance-tuning – EzLo Mar 28 at 12:12
  • @EzLo thank you for the link MSDB Performance Tuning – marcello miorelli Mar 28 at 12:28
  • @marcellomiorelli, just a thought, get the estimated execution plan and check before each insert to table variable you can get the estimated number rows, but not guaranteed but a good guess. – Biju jose Mar 28 at 13:34
4

My gripes with this proc go back a long way:

The problem you run into when deleting large amounts of data is the crappy estimate you get from the table variables.

I've had pretty good luck creating a new version of the proc using temp tables. You could also try just adding recompile hints, but hey, this way we get useful indexes.

As a side note: if you still run into this blocking because this is running long, you can try either removing the transaction code, or changing it to encapsulate each individual delete (though at that point the benefits are negligible).

CREATE   PROCEDURE [dbo].[sp_delete_backuphistory_pro]
   @oldest_date datetime
 AS
 BEGIN
   SET NOCOUNT ON

   CREATE TABLE #backup_set_id      (backup_set_id INT PRIMARY KEY CLUSTERED)
   CREATE TABLE #media_set_id       (media_set_id INT PRIMARY KEY CLUSTERED)
   CREATE TABLE #restore_history_id (restore_history_id INT PRIMARY KEY CLUSTERED)

   INSERT INTO #backup_set_id WITH (TABLOCKX) (backup_set_id)
   SELECT DISTINCT backup_set_id
   FROM msdb.dbo.backupset
   WHERE backup_finish_date < @oldest_date

   INSERT INTO #media_set_id WITH (TABLOCKX) (media_set_id)
   SELECT DISTINCT media_set_id
   FROM msdb.dbo.backupset
   WHERE backup_finish_date < @oldest_date

   INSERT INTO #restore_history_id WITH (TABLOCKX) (restore_history_id)
   SELECT DISTINCT restore_history_id
   FROM msdb.dbo.restorehistory
   WHERE backup_set_id IN (SELECT backup_set_id
                           FROM   #backup_set_id)

   BEGIN TRANSACTION

   DELETE FROM msdb.dbo.backupfile
   WHERE backup_set_id IN (SELECT backup_set_id
                           FROM   #backup_set_id)
   IF (@@error > 0)
     GOTO Quit

   DELETE FROM msdb.dbo.backupfilegroup
   WHERE backup_set_id IN (SELECT backup_set_id
                           FROM   #backup_set_id)
   IF (@@error > 0)
     GOTO Quit

   DELETE FROM msdb.dbo.restorefile
   WHERE restore_history_id IN (SELECT restore_history_id
                                FROM   #restore_history_id)
   IF (@@error > 0)
     GOTO Quit

   DELETE FROM msdb.dbo.restorefilegroup
   WHERE restore_history_id IN (SELECT restore_history_id
                                FROM   #restore_history_id)
   IF (@@error > 0)
     GOTO Quit

   DELETE FROM msdb.dbo.restorehistory
   WHERE restore_history_id IN (SELECT restore_history_id
                                FROM   #restore_history_id)
   IF (@@error > 0)
     GOTO Quit

   DELETE FROM msdb.dbo.backupset
   WHERE backup_set_id IN (SELECT backup_set_id
                           FROM   #backup_set_id)
   IF (@@error > 0)
     GOTO Quit

   DELETE msdb.dbo.backupmediafamily
   FROM msdb.dbo.backupmediafamily bmf
   WHERE bmf.media_set_id IN (SELECT media_set_id
                              FROM   #media_set_id)
     AND ((SELECT COUNT(*)
           FROM msdb.dbo.backupset
           WHERE media_set_id = bmf.media_set_id) = 0)
   IF (@@error > 0)
     GOTO Quit

   DELETE msdb.dbo.backupmediaset
   FROM msdb.dbo.backupmediaset bms
   WHERE bms.media_set_id IN (SELECT media_set_id
                              FROM   #media_set_id)
     AND ((SELECT COUNT(*)
           FROM msdb.dbo.backupset
           WHERE media_set_id = bms.media_set_id) = 0)
   IF (@@error > 0)
     GOTO Quit

   COMMIT TRANSACTION
   RETURN

 Quit:
   ROLLBACK TRANSACTION

 END
1

Here is something you could try.

  • Restore a backup of your MSDB database to a test server and call it something like MSDB_TEST.
  • Once restored, go into the sp_delete_backuphistory stored procedure in the MSDB_TEST database and search/replace msdb. with msdb_test. and alter it.
  • Capture the current row count of the tables you are interested in.
  • Now, run the altered version of the sp_delete_backuphistory stored procedure in the MSDB_TEST database.
  • Compare the current row counts to the previously capture ones.

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.