Delete data from large msdb database

• obtllc

  SSC-Addicted

  Points: 442

  More actions

  March 9, 2010 at 2:34 pm

  #139279

  My msdb database is almost 30 GB and I am having difficulty deleting old history files. It is taking forever.

  I got the sp_DeleteBackupHistory procedure from the forum, and it too is taking a long time to delete the data

  Here are the row counts of the tables:

  backupset12271157

  backupmediaset12263875

  backupmediafamily12251622

  backupfile1869831

  Is there a faster way to delete the data from the tables?

  Thanks

• Jeff Moden

  SSC Guru

  Points: 1004407

  More actions

  March 9, 2010 at 7:54 pm

  #1131092

  See the following:

  http://www.sqlservercentral.com/Forums/Topic187917-5-1.aspx

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)
  

• Bhuvnesh

  SSC Guru

  Points: 59351

  More actions

  March 10, 2010 at 3:42 am

  #1131243

  Jeff Moden (3/9/2010)

  ---

  See the following:

  http://www.sqlservercentral.com/Forums/Topic187917-5-1.aspx

  Very nice post

  -------Bhuvnesh----------
  I work only to learn Sql Server...though my company pays me for getting their stuff done;-)