473,573 Members | 4,503 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 4316
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
2312
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...
2
2096
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...
6
1730
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
1371
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
10819
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...
12
11751
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...
5
3468
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
5860
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...
3
1890
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
7741
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...
0
8165
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...
1
7730
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...
0
8026
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...
1
5550
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...
0
5252
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...
1
2163
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
1256
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
0
984
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...

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.