How to minimizeplan ahead the effect of running sp_delete_backuphistory? Measure the gains too! The Next CEO of Stack OverflowOptimization: Delete with Top and minimize the where clause date rangeHow to automatically stagger transaction log shipping to minimize bandwidth usage peaks?How to avoid using variables in WHERE clauseHow to give the least permissions for CRUD-managing and running SQLServerAgent jobs as well as Maintenance Plans?How to measure or find cost of creating a query plan?how to minimize the effect of updating statistics on the plan cache?A methodology for tracing a query that sporadically runs for hours instead of secondsRunning queries one after the other in psql/scripting environment?Query performance - lots of temptables, loops, updates and calculations for statistical reportMeasure Agent Job failure and running jobs with 'execution_status'

How to avoid supervisors with prejudiced views?

If a black hole is created from light, can this black hole then move at speed of light?

What happened in Rome, when the western empire "fell"?

How did people program for Consoles with multiple CPUs?

Why is the US ranked as #45 in Press Freedom ratings, despite its extremely permissive free speech laws?

Is it professional to write unrelated content in an almost-empty email?

Is "for causing autism in X" grammatical?

How to make a variable always equal to the result of some calculations?

Limits on contract work without pre-agreed price/contract (UK)

Complex fractions

If/When UK leaves the EU, can a future goverment conduct a referendum to join the EU?

What exact does MIB represent in SNMP? How is it different from OID?

Would a galaxy be visible from outside, but nearby?

How to invert MapIndexed on a ragged structure? How to construct a tree from rules?

Different harmonic changes implied by a simple descending scale

How do I go from 300 unfinished/half written blog posts, to published posts?

Are there any unintended negative consequences to allowing PCs to gain multiple levels at once in a short milestone-XP game?

WOW air has ceased operation, can I get my tickets refunded?

Example of a Mathematician/Physicist whose Other Publications during their PhD eclipsed their PhD Thesis

Novel about a guy who is possessed by the divine essence and the world ends?

Unreliable Magic - Is it worth it?

How to start emacs in "nothing" mode (`fundamental-mode`)

multiple labels for a single equation

Would this house-rule that treats advantage as a +1 to the roll instead (and disadvantage as -1) and allows them to stack be balanced?



How to minimizeplan ahead the effect of running sp_delete_backuphistory? Measure the gains too!



The Next CEO of Stack OverflowOptimization: Delete with Top and minimize the where clause date rangeHow to automatically stagger transaction log shipping to minimize bandwidth usage peaks?How to avoid using variables in WHERE clauseHow to give the least permissions for CRUD-managing and running SQLServerAgent jobs as well as Maintenance Plans?How to measure or find cost of creating a query plan?how to minimize the effect of updating statistics on the plan cache?A methodology for tracing a query that sporadically runs for hours instead of secondsRunning queries one after the other in psql/scripting environment?Query performance - lots of temptables, loops, updates and calculations for statistical reportMeasure Agent Job failure and running jobs with 'execution_status'










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











share|improve this question

















  • 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
    yesterday











  • @EzLo thank you for the link MSDB Performance Tuning

    – marcello miorelli
    yesterday











  • @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
    yesterday















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











share|improve this question

















  • 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
    yesterday











  • @EzLo thank you for the link MSDB Performance Tuning

    – marcello miorelli
    yesterday











  • @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
    yesterday













2












2








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











share|improve this question














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








sql-server backup delete scripting monitoring






share|improve this question













share|improve this question











share|improve this question




share|improve this question










asked yesterday









marcello miorellimarcello miorelli

5,9802163144




5,9802163144







  • 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
    yesterday











  • @EzLo thank you for the link MSDB Performance Tuning

    – marcello miorelli
    yesterday











  • @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
    yesterday












  • 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
    yesterday











  • @EzLo thank you for the link MSDB Performance Tuning

    – marcello miorelli
    yesterday











  • @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
    yesterday







2




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
yesterday





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
yesterday













@EzLo thank you for the link MSDB Performance Tuning

– marcello miorelli
yesterday





@EzLo thank you for the link MSDB Performance Tuning

– marcello miorelli
yesterday













@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
yesterday





@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
yesterday










2 Answers
2






active

oldest

votes


















4














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





share|improve this answer
































    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.





    share|improve this answer























      Your Answer








      StackExchange.ready(function()
      var channelOptions =
      tags: "".split(" "),
      id: "182"
      ;
      initTagRenderer("".split(" "), "".split(" "), channelOptions);

      StackExchange.using("externalEditor", function()
      // Have to fire editor after snippets, if snippets enabled
      if (StackExchange.settings.snippets.snippetsEnabled)
      StackExchange.using("snippets", function()
      createEditor();
      );

      else
      createEditor();

      );

      function createEditor()
      StackExchange.prepareEditor(
      heartbeatType: 'answer',
      autoActivateHeartbeat: false,
      convertImagesToLinks: false,
      noModals: true,
      showLowRepImageUploadWarning: true,
      reputationToPostImages: null,
      bindNavPrevention: true,
      postfix: "",
      imageUploader:
      brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
      contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
      allowUrls: true
      ,
      onDemand: true,
      discardSelector: ".discard-answer"
      ,immediatelyShowMarkdownHelp:true
      );



      );













      draft saved

      draft discarded


















      StackExchange.ready(
      function ()
      StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f233375%2fhow-to-minimize-plan-ahead-the-effect-of-running-sp-delete-backuphistory-measur%23new-answer', 'question_page');

      );

      Post as a guest















      Required, but never shown

























      2 Answers
      2






      active

      oldest

      votes








      2 Answers
      2






      active

      oldest

      votes









      active

      oldest

      votes






      active

      oldest

      votes









      4














      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





      share|improve this answer





























        4














        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





        share|improve this answer



























          4












          4








          4







          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





          share|improve this answer















          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






          share|improve this answer














          share|improve this answer



          share|improve this answer








          edited yesterday

























          answered yesterday









          Erik DarlingErik Darling

          22.4k1269111




          22.4k1269111























              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.





              share|improve this answer



























                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.





                share|improve this answer

























                  1












                  1








                  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.





                  share|improve this answer













                  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.






                  share|improve this answer












                  share|improve this answer



                  share|improve this answer










                  answered yesterday









                  Scott HodginScott Hodgin

                  17.9k21634




                  17.9k21634



























                      draft saved

                      draft discarded
















































                      Thanks for contributing an answer to Database Administrators Stack Exchange!


                      • Please be sure to answer the question. Provide details and share your research!

                      But avoid


                      • Asking for help, clarification, or responding to other answers.

                      • Making statements based on opinion; back them up with references or personal experience.

                      To learn more, see our tips on writing great answers.




                      draft saved


                      draft discarded














                      StackExchange.ready(
                      function ()
                      StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f233375%2fhow-to-minimize-plan-ahead-the-effect-of-running-sp-delete-backuphistory-measur%23new-answer', 'question_page');

                      );

                      Post as a guest















                      Required, but never shown





















































                      Required, but never shown














                      Required, but never shown












                      Required, but never shown







                      Required, but never shown

































                      Required, but never shown














                      Required, but never shown












                      Required, but never shown







                      Required, but never shown







                      Popular posts from this blog

                      រឿង រ៉ូមេអូ និង ហ្ស៊ុយលីយេ សង្ខេបរឿង តួអង្គ បញ្ជីណែនាំ

                      Crop image to path created in TikZ? Announcing the arrival of Valued Associate #679: Cesar Manara Planned maintenance scheduled April 17/18, 2019 at 00:00UTC (8:00pm US/Eastern)Crop an inserted image?TikZ pictures does not appear in posterImage behind and beyond crop marks?Tikz picture as large as possible on A4 PageTransparency vs image compression dilemmaHow to crop background from image automatically?Image does not cropTikzexternal capturing crop marks when externalizing pgfplots?How to include image path that contains a dollar signCrop image with left size given

                      Romeo and Juliet ContentsCharactersSynopsisSourcesDate and textThemes and motifsCriticism and interpretationLegacyScene by sceneSee alsoNotes and referencesSourcesExternal linksNavigation menu"Consumer Price Index (estimate) 1800–"10.2307/28710160037-3222287101610.1093/res/II.5.31910.2307/45967845967810.2307/2869925286992510.1525/jams.1982.35.3.03a00050"Dada Masilo: South African dancer who breaks the rules"10.1093/res/os-XV.57.1610.2307/28680942868094"Sweet Sorrow: Mann-Korman's Romeo and Juliet Closes Sept. 5 at MN's Ordway"the original10.2307/45957745957710.1017/CCOL0521570476.009"Ram Leela box office collections hit massive Rs 100 crore, pulverises prediction"Archived"Broadway Revival of Romeo and Juliet, Starring Orlando Bloom and Condola Rashad, Will Close Dec. 8"Archived10.1075/jhp.7.1.04hon"Wherefore art thou, Romeo? To make us laugh at Navy Pier"the original10.1093/gmo/9781561592630.article.O006772"Ram-leela Review Roundup: Critics Hail Film as Best Adaptation of Romeo and Juliet"Archived10.2307/31946310047-77293194631"Romeo and Juliet get Twitter treatment""Juliet's Nurse by Lois Leveen""Romeo and Juliet: Orlando Bloom's Broadway Debut Released in Theaters for Valentine's Day"Archived"Romeo and Juliet Has No Balcony"10.1093/gmo/9781561592630.article.O00778110.2307/2867423286742310.1076/enst.82.2.115.959510.1080/00138380601042675"A plague o' both your houses: error in GCSE exam paper forces apology""Juliet of the Five O'Clock Shadow, and Other Wonders"10.2307/33912430027-4321339124310.2307/28487440038-7134284874410.2307/29123140149-661129123144728341M"Weekender Guide: Shakespeare on The Drive""balcony"UK public library membership"romeo"UK public library membership10.1017/CCOL9780521844291"Post-Zionist Critique on Israel and the Palestinians Part III: Popular Culture"10.2307/25379071533-86140377-919X2537907"Capulets and Montagues: UK exam board admit mixing names up in Romeo and Juliet paper"Istoria Novellamente Ritrovata di Due Nobili Amanti2027/mdp.390150822329610820-750X"GCSE exam error: Board accidentally rewrites Shakespeare"10.2307/29176390149-66112917639"Exam board apologises after error in English GCSE paper which confused characters in Shakespeare's Romeo and Juliet""From Mariotto and Ganozza to Romeo and Guilietta: Metamorphoses of a Renaissance Tale"10.2307/37323537323510.2307/2867455286745510.2307/28678912867891"10 Questions for Taylor Swift"10.2307/28680922868092"Haymarket Theatre""The Zeffirelli Way: Revealing Talk by Florentine Director""Michael Smuin: 1938-2007 / Prolific dance director had showy career"The Life and Art of Edwin BoothRomeo and JulietRomeo and JulietRomeo and JulietRomeo and JulietEasy Read Romeo and JulietRomeo and Julieteeecb12003684p(data)4099369-3n8211610759dbe00d-a9e2-41a3-b2c1-977dd692899302814385X313670221313670221