Styjun

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.