Update Data Optimization job locked the server

Update Data Optimization job locked the server

Post by Yan L » Wed, 04 Jul 2001 01:10:08


I have a database about 4GB in size runing on SQL2000
(SP1)/Windows 2000. The machine has 1GB memory, in which,
900MB is allocated to SQL through dynamical configuration.

A scheduled maintenance job is created via the standard
SQL maintenance planner tool. In this job, only Update
Data Optimization is selected to run against the database,
and everytime when this job run it freezed the whole
server - all screens + client accesses + the keyboard.

My understanding about the Optimzation is that SQL Server
will basically do the DBREINDEX for each index of every
table in the database. So, I picked the largest table (
about 2 million rows ) to run DBCC DBREINDEX manually
against every index in the table using the Query Analyzer.
They were all fine. However, when to run DBREINDEX against
the whole database, it freezed server.

I noticed that when I run DBREINDEX against each index,
the CPU and Physical I/O for that statement climbed
extreamlly fast.

Interesting enough or confusing enough, some optimizatoin
jobs for other databases on the same server run fine.  I
did all sorts of DBCC checks against the database I had
problem with, they all returned no error.  

Any one knows anything about this?  Or it might be a SQL



1. Update Data Optimization Information

I'm using SQLServer2000 Database Maintainence Plan wizard
and am on the screen that allows me to Update Data
Optimization Information.

I would like to run statistics so that the queries run
against the database are optimized. I also think it is
important to refactor the fill factor for the indexes so
that updates are optimized.

I was wondering why this choice seems to be mutually



2. question on Database design in SQL Server


4. Selecting all children of a self-referencing table

5. Problem locking data using FOR UPDATE

6. SQL 7.0 install problem on Win2000 PRO

7. UPDATE to data is locking LARGE table for a LONG time

8. help: repair/reinstall sysmon

9. optimization job filling up log?

10. Optimization Job for DB Maint Plan Sunday failed 10/21/01 1:00:03am

11. Appications lockup while performing Optimization job

12. Maintenance Plan Optimizations --> Job Steps

13. Optimization Job for DB Maint Plan Sunday failed 10/21/01 1:00:03am