Hcfyk

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.

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

My gripes with this proc go back a long way:

• The Annals of Hilariously Bad Code, Part 1: Critique the Code


• The Annals of Hilariously Bad Code, Part 2

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

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.