Gui Interface...

• 
  Hello Shawn,
  
  I think that more than likely, SQL Backup is running out of buffer space when trying to pull the backup history from your msdb database. Does this sound possible?
  
  I'll see if I can come up with a query to trim the number of entries for the backup history if that's the case.

  Brian Donahue July 18, 2005 13:30

  - 0 +
• 
  Could be...the msdb.backupfile, msdb.backupmediafamily, msdb.backupmediaset databases are rather huge (about 3.6 million records each).

  swjohnson July 18, 2005 15:00

  - 0 +
• 
  Brian's right about running out of space to hold that number of records. To clear the backup history records, use the sp_delete_backuphistory stored procedure.

  petey July 18, 2005 21:47

  - 0 +
• 
  Yes, it was probably the number of entries in all the msdb.backupmediaset, msdb.backupmediafamily, msdb.backupset, msdb.backupfile tables.
  
  I started to run the sp_delete_backuphistory Microsoft supplied stored procedure but I learned that it uses a cursor and that it was going to take about forever to delete all but 5 days of history in my msdb database (my db had about 3.8 million records), I found a neat little stored procedure that does it in a set based format that you use as a stored procedure and run through a job and keep the maintenance up-to-date.
  
  http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1704.aspx
  
  I know that your extended stored procedure can delete files after x number of days (erasefiles=x). Does it also clean up those entries in the tables above?

  swjohnson July 21, 2005 10:13

  - 0 +
• 
  >> Does it also clean up those entries in the tables above?
  No it doesn't.

  petey July 21, 2005 11:14

  - 0 +

Add comment

Please sign in to leave a comment.