Re: Periodic maintenance like sp_delete_backuphistory?
From: Mark Allison (marka_at_no.tinned.meat.mvps.org)
Date: 10/04/04
- Next message: Cesar: "Stored Procedure Slow"
- Previous message: Vikrant V Dalwale [MSFT]: "Re: Error cause job to quit running"
- In reply to: Michael Bourgon: "Periodic maintenance like sp_delete_backuphistory?"
- Messages sorted by: [ date ] [ thread ]
Date: Mon, 4 Oct 2004 17:49:53 +0100
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.com... > 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.
- Next message: Cesar: "Stored Procedure Slow"
- Previous message: Vikrant V Dalwale [MSFT]: "Re: Error cause job to quit running"
- In reply to: Michael Bourgon: "Periodic maintenance like sp_delete_backuphistory?"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
