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
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