lozzamoore
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,
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,
ASKER
Thanks CollinSnelling,
I'll need to do some testing on this, I'll get back to you.
First inspection looks good.
Cheers,
I'll need to do some testing on this, I'll get back to you.
First inspection looks good.
Cheers,
ASKER
Thanks CollinSnelling,
I'll need to do some testing on this, I'll get back to you.
First inspection looks good.
Cheers,
I'll need to do some testing on this, I'll get back to you.
First inspection looks good.
Cheers,
ASKER
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,
The line:
>select * from backupset where
is missing the where clause.
Don't think this line needs to be there(?)
Cheers,
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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,
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.
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.
Hi,
In the maintenance plans there is a setting of how many rows of history to keep. It defaults to 1000.
Regards
David
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.
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
But what say you do the backup via a maintenance plan? Would both tables be involved?
Regards
David
ASKER
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,
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
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.
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.
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_back
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