473,703 Members | 5,073 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Periodic maintenance like sp_delete_backu phistory?

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_backu phistory (leaving the last year's data) - what else is
there?
P.S. to anyone else who needs to use sp_delete_backu phistory - 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 3547
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.goo gle.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_backu phistory (leaving the last year's data) - what else is
there?
P.S. to anyone else who needs to use sp_delete_backu phistory - 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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
2468
by: Hans Forbrich | last post by:
The following is a Request for Discussion, following up on some recent posts about distributing a periodic news group 'Charter and FAQ' post. While not formally following RFC/RFD rules, the intent is to use the rules as a guide for the discussion and make a more formal Request for Comment and a follow-up formal vote, possibly before year-end. Proposal: That the following be posted weekly for this news group:
0
1841
by: Hans Forbrich | last post by:
Section 1. Ballot: ------------------- 1.YES NO: I agree that there should be a periodic post describing the newsgroup charter and providing a FAQ on newsgroup usage. 2.MONTHLY BI-WEEKLY WEEKLY: The post should be transmitted with this frequency.
0
1822
by: Hans Forbrich | last post by:
Section 1. Ballot: ------------------- 1.YES NO: I agree that there should be a periodic post describing the newsgroup charter and providing a FAQ on newsgroup usage. 2.MONTHLY BI-WEEKLY WEEKLY: The post should be transmitted with this frequency.
20
2814
by: Karl Smith | last post by:
I heard a rumour that Opera succeeded where none have before, and implemented the tables described in HTML4 and CSS2. So I thought I'd try it out with the well known Periodic Table. http://users.tpg.com.au/karl6740/css/table_elements_periodic.html CSS: Notice in the TRs with the lanthanides and actinides, the empty TDs at the end taking the background colour of the TR? I say they shouldn't
8
2733
by: Stephen Rice | last post by:
Hi, I have a periodic problem which I am having a real time trying to sort. Background: An MDI VB app with a DB on SQL 2000. I have wrapped all the DB access into an object which spawns a thread to access the database and then displays a modal dialog which allows the user to cancel the task, if it is taking longer than they want, and shows them a display of how long the query has been running so far.
5
8906
by: nishantxl | last post by:
Hi there, I am looking to design a project using C++ The main objective of the project is to display details of periodic table elements such as periodic element name, properties(such as atomic number and atomic mass) for each periodic number entered by user. I am thinking to input all the data regarding each periodic element number in form of class. Please give your opinion on the correct design method for the same.
0
1222
by: Mark D Powell | last post by:
I recently learned that there is a stored procedure sp_delete_backuphistory that cleans up the msdb for information related to backup jobs. I tried the procedure in test and it ran 5 hours with a target date of the first day in 2003. Obviously my databases have been around a while. Up until now I thought the limit values entered into the EM maintenance plan limited the history that SQL Server keep. Obviously I was wrong. I am...
47
1701
by: editormt | last post by:
Software maintenance is an important part of the software development activity, but it is also the less discussed. A recent poll seems to show that the part of maintenance in software development budget is going down. Why? Question: what percentage of your software development budget is devoted to maintenance. Maintenance is defined as process of correcting, enhancing and optimising deployed software. 25% or less of the budget...
4
3363
by: Mike Gleason jr Couturier | last post by:
Hi, What's a clean way to redirect users on an information page while doing site maintenance... Can we do it with urlMappings !? (wildcard "*"?) Thanks
0
8760
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
8670
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
9254
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
7872
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
6592
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
5923
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
4433
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
4687
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
3125
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.