469,106 Members | 2,267 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,106 developers. It's quick & easy.

Periodic maintenance like sp_delete_backuphistory?

I'd like to remove any cruft that's built up in the system over the
last couple of years. Obviously there's old information no longer
needed, I'm just not sure what is there.

I've been cleaning up backup history in MSDB with
sp_delete_backuphistory (leaving the last year's data) - what else is
there?
P.S. to anyone else who needs to use sp_delete_backuphistory - if you
have a lot of backups, you really need to add some indexes, otherwise
it'll take days to remove the history. Go through the SP and figure
out which tables/fields you need to index, as there are several. The
biggies are (in multiple tables) media_set_id, backup_set_id, and
restore_history_id.
Jul 20 '05 #1
1 3373
Michael ,

Have a look at the sizes of your system databases. msdb on one of my servers
is 900MB. That's all down to DTS packages though (mostly). SQL Server does a
pretty good job of clearing up after itself. You may want to have a quick
look in the Logs folder, and if you use replication, the REPLDATA folder.
Also, check that the Windows Event log is big enough - you can size that in
Administrative Tools outside of SQL Server.

The biggest bangs for your buck for your housekeeping expedition might be to
manage your user databases' audit tables - possibly even create a process
where your data gets horizontally partitioned and moved to another
historical database. This will improve performance in your main user
database.
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk

Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
"Michael Bourgon" <bo*****@gmail.com> wrote in message
news:55**************************@posting.google.c om...
I'd like to remove any cruft that's built up in the system over the
last couple of years. Obviously there's old information no longer
needed, I'm just not sure what is there.

I've been cleaning up backup history in MSDB with
sp_delete_backuphistory (leaving the last year's data) - what else is
there?
P.S. to anyone else who needs to use sp_delete_backuphistory - if you
have a lot of backups, you really need to add some indexes, otherwise
it'll take days to remove the history. Go through the SP and figure
out which tables/fields you need to index, as there are several. The
biggies are (in multiple tables) media_set_id, backup_set_id, and
restore_history_id.

Jul 20 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

2 posts views Thread by Hans Forbrich | last post: by
reply views Thread by Hans Forbrich | last post: by
reply views Thread by Hans Forbrich | last post: by
5 posts views Thread by nishantxl | last post: by
reply views Thread by Mark D Powell | last post: by
4 posts views Thread by Mike Gleason jr Couturier | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by kglaser89 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.