Microsoft SQL server 2000 Maintenance Jobs

sglee
sglee used Ask the Experts™
on
Database  Maintenance PlansHi,
 
 I have Microsoft SQL Server with 30GB of database and there are several DB Maintenance plans:
DB Maintenance Plan 1: It performs Optimization (runs 1am daily) and Integrity Checks (runs at 9:30pm daily).
DB Maintenance Plan 2: It backs up main SQL database to the shared folder off another PC on the network (runs at 10pm daily).
Transaction Log Backup: It backs up TRN files to the shared folder off another PC on the network every 30 minutes throughout the day.

1. What does Optimization and Integrity Checks?
2. Do they have to run Daily?
3. Between Database backup, Optimization and  Integrity Checks, what should be the right order of running these jobs as far as scheduling goes?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
bbaoIT Consultant

Commented:
are you sure it is a SQL Server 2000? still running on Windows 2000?

Author

Commented:
Yes it is MS SQL 2000 running on Windows Server 2003.
"Batchelor", Developer and EE Topic Advisor
Top Expert 2015
Commented:
Putting aside anything we have to say because of that very, very old software gone long out of service ...
Optimization reindexes the database, using as much as possible of the existing index data (versus doing a rebuildy which drops and recreates indexes). This is usually a low impact operation, and can be done often.
Integrity checks are some basic checks, e.g. for data pages not written back to database correctly.

Both tasks can run daily. The current order is correct, but the integrity check might need more time before backup is started.

Author

Commented:
Is there way to determine how long Optimization  job took?
Qlemo"Batchelor", Developer and EE Topic Advisor
Top Expert 2015

Commented:
You should see that under SQL Server Agent, Logs and in the history of the individual task.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial