Solved

MSDB Backup history Becoming Unmanageable

Posted on 2002-06-12
14
825 Views
Last Modified: 2008-03-17
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,
0
Comment
Question by:lozzamoore
  • 5
  • 4
  • 2
  • +2
14 Comments
 
LVL 1

Expert Comment

by:ColinSnelling
ID: 7072706
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

0
 
LVL 7

Author Comment

by:lozzamoore
ID: 7074996
Thanks CollinSnelling,

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

First inspection looks good.

Cheers,
0
 
LVL 7

Author Comment

by:lozzamoore
ID: 7074998
Thanks CollinSnelling,

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

First inspection looks good.

Cheers,
0
 
LVL 7

Author Comment

by:lozzamoore
ID: 7074999
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,
0
 
LVL 1

Accepted Solution

by:
ColinSnelling earned 100 total points
ID: 7075036
Sorry about that.  I think that was a bit of testing code.
0
 
LVL 7

Author Comment

by:lozzamoore
ID: 7075983
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,
0
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 
LVL 1

Expert Comment

by:ColinSnelling
ID: 7077799
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.
0
 
LVL 35

Expert Comment

by:David Todd
ID: 7123626
Hi,

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

Regards
  David
0
 
LVL 1

Expert Comment

by:ColinSnelling
ID: 7124227
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.

0
 
LVL 35

Expert Comment

by:David Todd
ID: 7125548
Hi,

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

Regards
  David
0
 
LVL 7

Author Comment

by:lozzamoore
ID: 7126404
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,
0
 

Expert Comment

by:modulo
ID: 7427224
Dear: ColinSnelling

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

See:        http://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
0
 

Expert Comment

by:CleanupPing
ID: 9280345
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.
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Introduced in Microsoft SQL Server 2005, the Copy Database Wizard (http://msdn.microsoft.com/en-us/library/ms188664.aspx) is useful in copying databases and associated objects between SQL instances; therefore, it is a good migration and upgrade tool…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Viewers will learn how the fundamental information of how to create a table.

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

24 Experts available now in Live!

Get 1:1 Help Now