Link to home
Start Free TrialLog in
Avatar of lozzamoore
lozzamooreFlag for United Kingdom of Great Britain and Northern Ireland

asked on

MSDB Backup history Becoming Unmanageable

Hey guys,

We have a server with a large number of user databases. (currently 300 odd)

We have a bit of a job managing backup history in MSDB for this server.

I can't believe Microsoft missed this out of their Maintenance plans, but we must run the:
sp_delete_backuphistory every couple of months on this server which can take a day or so to run.
(It seems to take about 1 and a half hours to clear one day's worth!!)

Does anyone have any other experiences or information on dealing with this issue more effectively?

Thanks,
Avatar of ColinSnelling
ColinSnelling

I found the same problem when running sp_delete_backuphistory - it takes ages because it uses a cursor.

I wrote this instead.  It creates a stored procedure (in msdb) that deletes the backup/restore history without using a cursor.  Pass the date you want to delete up to as the parameter.

Colin


use msdb
GO

if object_id('usp_delete_backuphistory') > 0 drop procedure usp_delete_backuphistory
GO

CREATE PROCEDURE usp_delete_backuphistory @oldest_date datetime
AS
-- This is a quicker version of Microsoft's sp_delete_backuphistory
set nocount on

CREATE TABLE #msid
     (
     media_set_id     integer
     )

begin transaction
     delete from restorefile where restore_history_id in (select restore_history_id from restorehistory rh join backupset bs ON bs.backup_set_id = rh.backup_set_id where bs.backup_finish_date < @oldest_date)
     if @@error <> 0 goto QuitWithRollback

     delete from restorefilegroup where restore_history_id in (select restore_history_id from restorehistory rh join backupset bs ON bs.backup_set_id = rh.backup_set_id where bs.backup_finish_date < @oldest_date)
     if @@error <> 0 goto QuitWithRollback

     delete from restorehistory where backup_set_id in (select backup_set_id from backupset where backup_finish_date < @oldest_date)
     if @@error <> 0 goto QuitWithRollback

     insert into #msid select media_set_id from backupset where backup_finish_date < @oldest_date
     if @@error <> 0 goto QuitWithRollback

     delete from backupfile where backup_set_id in (select backup_set_id from backupset where backup_finish_date < @oldest_date)
     if @@error <> 0 goto QuitWithRollback

     delete backupmediafamily where media_set_id in (select media_set_id from #msid)
     if @@error <> 0 goto QuitWithRollback

     select * from backupset where
     delete from backupset where backup_finish_date < @oldest_date
     if @@error <> 0 goto QuitWithRollback

     delete backupmediaset where media_set_id in (select media_set_id from #msid)
     if @@error <> 0 goto QuitWithRollback

commit tran
print 'Successfully deleted history'
RETURN 0

QuitWithRollback:
rollback tran
print 'Error with process'
RETURN 1

Avatar of lozzamoore

ASKER

Thanks CollinSnelling,

I'll need to do some testing on this, I'll get back to you.

First inspection looks good.

Cheers,
Thanks CollinSnelling,

I'll need to do some testing on this, I'll get back to you.

First inspection looks good.

Cheers,
There is a syntax error in the command.
The line:
>select * from backupset where
is missing the where clause.

Don't think this line needs to be there(?)

Cheers,
ASKER CERTIFIED SOLUTION
Avatar of ColinSnelling
ColinSnelling

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
CollinSnelling,

This code appears to work well, and is certainly much quicker.

However, it is a fair bit of hassle to schedule this to run on all our servers, separate from the Maintenance Plans etc...

The backup history entries appear to be a lot of data, to give very little benefit.

I was wondering if there was any way of turning these entries being written off in the first place?

If no-one posts any more information or suggestions on this, I will accept your answer and go with this approach.

Cheers,
I did a quick search and can't find anything about reducing the volume of data stored with settings on the maintenance plan or server.  Quite happy to be proved wrong and learn something new though.

Can you set the job up as a multi-server task?  This isn't something I've ever used but as I understand it you designate one server as the master and the other servers as targets.  The job then runs on the master and target servers while only being defined once.

Look up 'Multiserver Administration' in Books Online for some better info on this.
Avatar of David Todd
Hi,

In the maintenance plans there is a setting of how many rows of history to keep. It defaults to 1000.

Regards
  David
I'm pretty sure that that's just for the maintenance plan history which isn't the same as the backup/restore history.

There are seperate tables for the maintenance plan histories.

Hi,

But what say you do the backup via a maintenance plan? Would both tables be involved?

Regards
  David
Collinsnelling is right dtodd.

The maintenance plan only allows you to limit the size of the maintenance plan history table.

The backup history table appears to be completely separate to this, and must be managed independently of maintenance plans.

Cheers,
Dear: ColinSnelling

I've rejected your proposed answer as Experts Exchange holds an experiment to work without the answer button.

See:        https://www.experts-exchange.com/jsp/communityNews.jsp
Paragraph: Site Update for Wednesday, November 06, 2002

By this rejection the Asker will be notified by mail and hopefully he will take his responsibility to finalize the question or post an additional comment.
The Asker sees a button beside every post which says "Accept This Comment As Answer" (including rejected answers) -- so if he/she thinks yours is the best, you'll be awarded the points and the grade.

Thanks !

modulo

Community Support Moderator
Experts Exchange
lozzamoore:
This old question needs to be finalized -- accept an answer, split points, or get a refund.  For information on your options, please click here-> http:/help/closing.jsp#1 
EXPERTS:
Post your closing recommendations!  No comment means you don't care.