SEO
vBulletin Search Engine Optimization
| |||||||
| Register | FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read |
02-29-2008, 04:26 AM
| ||||
| ||||
 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.      |
|
02-29-2008, 04:26 AM
| ||||
| ||||
| Re: Periodic maintenance like sp_delete_backuphistory? 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" <bourgon@gmail.com> wrote in message news:558b578d.0410040819.4df8846f@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. |
Linear Mode
