473,602 Members | 2,787 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 3536
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
2451
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
1836
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
1811
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
2792
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
2718
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
8889
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
1221
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
1688
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
3359
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
7921
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
8405
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
8404
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
0
6730
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...
0
5440
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
3901
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...
1
2424
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
1
1511
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
0
1256
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

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.