reindexing job taking too long

I have an optimization job on a 4 way SQL 2000 box that takes 7-12 hours.
The db is 150 gigs. I can't figure out why sometimes it finishes 5 hours
earlier than other times. Backups are not contending with this optimization
job, but users may be. How can I find this out?
TIA
jjAre there active processes locking this table during the index
creation/defragmentation/reindexing?
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"jj" <jeff_detoro@.urmc.rochester.edu> wrote in message
news:esxLN5m1GHA.4116@.TK2MSFTNGP02.phx.gbl...
>I have an optimization job on a 4 way SQL 2000 box that takes 7-12 hours.
>The db is 150 gigs. I can't figure out why sometimes it finishes 5 hours
>earlier than other times. Backups are not contending with this optimization
>job, but users may be. How can I find this out?
> TIA
> jj
>|||jj wrote:
> I have an optimization job on a 4 way SQL 2000 box that takes 7-12 hours.
> The db is 150 gigs. I can't figure out why sometimes it finishes 5 hours
> earlier than other times. Backups are not contending with this optimizatio
n
> job, but users may be. How can I find this out?
> TIA
> jj
>
You'll have to monitor blocking during the times that this process is
running, either with Profiler or with your own script that watches
sysprocesses.
Something to consider would be to not rebuild all indexes, but to only
rebuild those that are badly fragmented. I have a script that will
automate this for you:
http://realsqlguy.com/serendipity/a...realsqlguy.com