473,851 Members | 1,918 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Order of Database Maintenance Plans (SQL 7/2000)

I want to know if there is a "best-practice" for setting up Database
Maintenance Plans in SQL Server 7 or 2000. To be more specific, I want
to know the order in which I complete the tasks. Do I complete
optimization first, then integrity checks, then translog backup, then
full backup??? OR is there a better order which should be used?

Should I ALWAYS backup the transaction Log before I complete a full
database backup, and if so, why??

If someone can help, it would be great.....
Jul 20 '05 #1
2 4330
On 28 Oct 2003 15:57:10 -0800, ja**********@pe risher.com.au (trotter)
wrote:
I want to know if there is a "best-practice" for setting up Database
Maintenance Plans in SQL Server 7 or 2000. To be more specific, I want
to know the order in which I complete the tasks. Do I complete
optimization first, then integrity checks, then translog backup, then
full backup??? OR is there a better order which should be used?
Well, optimization means "changing things", and when you change
things, disasters are possible. Full backups come before
optimizations. Integrity *checks*, that is, looking to see if
everything is okay, should probably come before backup (you don't want
to back up bad data).
Should I ALWAYS backup the transaction Log before I complete a full
database backup, and if so, why??


Here's the deal on that.

The transaction log is a listing of what has been done (or is being
done) to the database. Once you've done a full backup, the transaction
log is no longer necessary to restore your database to the state it
was in as of the completion of the backup. So, if you backup the
database at 3:00am, the transaction log will do you no good in
restoring the database to the point that it was at 3:00am.

Okay... so why backup the transaction log at all?

Well, first, it backs up a lot faster than the database (though it's
slower to restore)... on a busy system, you might not be able to run
multiple full backups during the day, but you can probably run many
transaction log backups during the day.

Second, unless you tell it not to, the backup procedure will delete
all transactions that are already written to the database; this
reduces the amount of your log file that's in use, and keeps it from
growing.

So, once you've backed up the database, you don't need to backup the
transaction log to help restore the database, but you might want to
anyway, to delete the inactive portions.

If you're using the full database recovery model, you'll probably want
to take multiple transaction log backups during the day, both for
database recovery, and to keep the transaction log from filling up.
(Yes, it can autogrow, but it's better if it doesn't.)

--
Everything I needed to know in life I learned in Kindergarten. Like:
Once you pull the pin on Mr. Hand Grenade, he is no longer your friend.
Jul 20 '05 #2
On 28 Oct 2003 15:57:10 -0800, ja**********@pe risher.com.au (trotter)
wrote:
I want to know if there is a "best-practice" for setting up Database
Maintenance Plans in SQL Server 7 or 2000. To be more specific, I want
to know the order in which I complete the tasks. Do I complete
optimization first, then integrity checks, then translog backup, then
full backup??? OR is there a better order which should be used?
Well, optimization means "changing things", and when you change
things, disasters are possible. Full backups come before
optimizations. Integrity *checks*, that is, looking to see if
everything is okay, should probably come before backup (you don't want
to back up bad data).
Should I ALWAYS backup the transaction Log before I complete a full
database backup, and if so, why??


Here's the deal on that.

The transaction log is a listing of what has been done (or is being
done) to the database. Once you've done a full backup, the transaction
log is no longer necessary to restore your database to the state it
was in as of the completion of the backup. So, if you backup the
database at 3:00am, the transaction log will do you no good in
restoring the database to the point that it was at 3:00am.

Okay... so why backup the transaction log at all?

Well, first, it backs up a lot faster than the database (though it's
slower to restore)... on a busy system, you might not be able to run
multiple full backups during the day, but you can probably run many
transaction log backups during the day.

Second, unless you tell it not to, the backup procedure will delete
all transactions that are already written to the database; this
reduces the amount of your log file that's in use, and keeps it from
growing.

So, once you've backed up the database, you don't need to backup the
transaction log to help restore the database, but you might want to
anyway, to delete the inactive portions.

If you're using the full database recovery model, you'll probably want
to take multiple transaction log backups during the day, both for
database recovery, and to keep the transaction log from filling up.
(Yes, it can autogrow, but it's better if it doesn't.)

--
Everything I needed to know in life I learned in Kindergarten. Like:
Once you pull the pin on Mr. Hand Grenade, he is no longer your friend.
Jul 20 '05 #3

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

6
2328
by: teedilo | last post by:
We have an application with a SQL Server 2000 back end that is fairly database intensive -- lots of fairly frequent queries, inserts, updates -- the gamut. The application does not make use of performance hogs like cursors, but I know there are lots of ways the application could be made more efficient database-wise. The server code is running VB6 of all things, using COM+ database interfaces. There are some clustered and non-clustered...
2
2117
by: Matt | last post by:
I run SQL Server 2000 and use thier database maintenance plans to backup my databases. My questions is what happens if a change is being made to a database table while a backup is running? Should I be locking the databases before the backup begins? Scenario: 1) Database Plan begins backup at 7:00 PM. 2) At 7:01 PM a web user updates their password while the backup is taking place.
6
1755
by: sam | last post by:
I have to import data into a empty database, that has many tables. some tables have to be inserted first than others due to the foreign keys. How do I find out the order of the tables that I have to insert data into? Thanks in advance! Sam
0
1382
by: Larry Maturo | last post by:
I noticed that in SQL Server Management Studio, under Management, there is a Legacy folder with maintenace plans created by adding entries to the system tables dbo.sysmaintplan_* tables (i.e. all the maintenace plans created in sql server 2000). Where are the new maintenance plans kept? Is there any API interface to it? -- Larry Maturo
104
10921
by: Beowulf | last post by:
I have the view below and if I use vwRouteReference as the rowsource for a combo box in an MS Access form or run "SELECT * FROM vwRouteReference" in SQL Query Analyzer, the rows don't come through sorted by Numb. Everything I've read on the web suggests that including the TOP directive should enable ORDERY BY in views. Does someone have an idea why the sorting is not working correctly for this particular view? thanks. CREATE VIEW...
12
11776
by: hallpa1 | last post by:
Hi all, I posted messages before about trying to purge many records (about 35%) of a 200Gig database. The responses gave me a lot to think about, especially regarding the indexes. But due to the short windows that I have to run in, manipulating the indexes is not an option. But this leads to another question. When all of this is done, we will need to shrink the db to reclaim the space. We will also need to rebuild the indexes, but...
5
3481
by: Icemokka | last post by:
Hi, We have a SQL-cluster with over 6300 databases, most of them pretty small in size and usage. For this reason all these databases are on auto-close ( Otherwise SQL would need many many gigabytes ). We've recently upgraded to SQL 2005 because of the Management Studio, because the EM is unusable on a SQL-Server with lots of databases.
5
5870
by: bosko19 | last post by:
We have recently set up two SQL Server 2005 Standard Edition servers with database mirroring. The mirrors function and fail over without a problem, but the Database Mirroring Monitor Job fails every time with the following error message: Incorrect syntax near '.'. (Error 102) Incorrect syntax near the keyword 'with'. If this statement is a common table expression or an xmlnamespaces clause, the previous statement must be terminated...
3
1901
by: David Greenberg | last post by:
Hi We use Sql 2000. When defining a database maintenance plan one of the options is to optimize the database, reorganize data and index pages, remove unused pages etc. Is there a command to do this manualy (or in a job) and not by usung a DBM plan ?
0
9898
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...
1
10735
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
10356
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
9506
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
7907
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
7073
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
5736
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
4549
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
3
3180
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.