History Cleanup Task

• JDC-286127

  Mr or Mrs. 500

  Points: 503

  More actions

  January 21, 2009 at 7:39 pm

  #125788

  I am trying to run the history cleanup task once, our history is a bit overboard at this point. However, when I run the task my C:\ drive fills up quickly and it is related to the msdb.ldf file growing out of control. The recovery mode is set to simple, I've tried bulk logged as well. I've even desperately tried auto shrink (I know scary).

  I'm looking for a suggestion on the best way to run the task without this ldf growing and filling up c:\, I suspect I could move it but I was hoping for no downtime. Is there a way to run it without any transactions recorded to the log. This is a predefined maintenance plan in SQL Server 2005.

  Thanks in advance everyone.

• happycat59

  One Orange Chip

  Points: 29389

  More actions

  January 21, 2009 at 7:52 pm

  #930851

  Can you add another log file on a different partition ? You could do this, run your history maintenance and then remove the log file you added

• JamieX

  Hall of Fame

  Points: 3763

  More actions

  January 22, 2009 at 8:54 am

  #931231

  deleting a bit at a time might help you. Say you have 2 years worth of history (730 days), delete 2-4 weeks at a time..

  sp_delete_backuphistory (#days to keep)

  so find out how many days back you have and take off 14-30 days at a time

  exec sp_delete_backuphistory 730

  exec sp_delete_backuphistory 700

  exec sp_delete_backuphistory 670

  etc..

• spongemagnet

  SSCrazy

  Points: 2812

  More actions

  January 22, 2009 at 9:23 am

  #931264

  Just to add a potentially helpful tidbit.. your backup history tables are an awesome resource to go back and report on database size/growth. If you need to report on this information ever, and you don't also collect these statistics elsewhere you might want to consider exporting them somewhere that you could still access them if need be - before deleting them.

  If you totally don't need or care for the data and deleting just isn't working you can truncate the backup and restore history tables, perhaps the job history table too.

  
  -Ken

• MANU-J.

  SSC-Dedicated

  Points: 31126

  More actions

  January 22, 2009 at 5:35 pm

  #931563

  Make use of this sample code:

  Deleting records from 600 to 100 days old:-

  USE MSDB

  DECLARE @backup_date DATETIME

  DECLARE @countback int

  set @countback = 600

  while @countback >= 100

  BEGIN

  print @countback

  set @backup_date=(select dateadd (dd, -@countback, getDate()))

  BEGIN TRAN DELE

  EXEC SP_DELETE_BACKUPHISTORY @backup_date

  COMMIT TRAN DELE

  set @countback = @countback - 2

  END

  The above code is deleting records that are 600 to 100 days old, deleting 2 days at a time.

  MJ