如何最小化提前計劃執行 sp_delete_backuphistory 的效果？也衡量收益！

執行以下查詢時：

-- DATEADD (datepart , number , date )  

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

我得到很多鎖和塊，可能是因為這個命令已經有一段時間沒有執行了，如果有的話。

在我實際執行或計劃執行此命令之前，有沒有辦法找出每個涉及的表中有多少要刪除？

我使用dateadd來計算 6 個月。

在sp_delete_backuphistory中，以下表格被修整：

sp_delete_backuphistory 必須從 msdb 數據庫執行並影響以下表：

備份文件

備份文件組

備份媒體家族

備份媒體集

備份集

恢復文件

恢復文件組

恢復歷史

我對這個過程的抱怨可以追溯到很久以前：

• 非常糟糕的程式碼編年史，第 1 部分：批評程式碼
• 非常糟糕的程式碼編年史，第 2 部分

刪除大量數據時遇到的問題是從表變數中得到的糟糕估計。

我很幸運地使用臨時表創建了一個新版本的 proc。您也可以嘗試僅添加重新編譯提示，但是，嘿，這樣我們可以獲得有用的索引。

附帶說明：如果您仍然遇到此阻塞，因為它執行時間很長，您可以嘗試刪除事務程式碼，或更改它以封裝每個單獨的刪除（儘管此時好處可以忽略不計）。

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

引用自：https://dba.stackexchange.com/questions/233375