How to determine the progress DBREINDEX

I have executed a reindex on a large table. While I started it during and off peak time, it is nearing a peak time and it is not finished. Is there anyway for me to find out what the progress is on the process so that I can estimate when it will finish? It has been running for approximately 10 hours now.

I do know from my research that if I would cancel it now, it would have to rollback all changes and would most likely take another 10 hours. I am satisfied the best option is to push through, but would like to have an idea of how long that may be.

Would I be able to tell by the size of the database file?
dgerlerAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

chapmandewCommented:
In 2000, not really, no.  In 2005, you can use some of the dynamic management views to get an IDEA for the progress, but it can't tell you for sure.

You can't really tell by the size of the file, other than "a big one will take longer than a small one"...a lot of factors come into play, such as the activity on the server at the time.  Does that make sense?  Its really not a cut and dry operation.
0
dgerlerAuthor Commented:
I have another server that has sql 2005 on it. Would I be able to use the dynamic managment views to see anything via a connection from another server?
0
chapmandewCommented:
No, the problem is that the DMVs only report info for 2005 instances...they wouldn't have info for the 2000 machine....
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

dgerlerAuthor Commented:
I figured something like that, but I had to ask.

The file for the table being reindexed is usually about 40 GB. I would say that probably 2/3 of that size can be attributed to the table beinig reindexed. Would you say that the file should not grow to more than 80 GB? It is current at about 69 GB.
0
dgerlerAuthor Commented:
I mean that 2/3 of the data in the file is in that table with 1/3 being in other tables.
0
chapmandewCommented:
No way for me to be able to tell that....sorry.
0
dgerlerAuthor Commented:
I appreciate the information you have provided.

Thank You.
0
chapmandewCommented:
Welcome....it would be a neat feature for them to add the progress on it....it just doesn't exist right now.  Sorry.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
dgerlerAuthor Commented:
Do you have a towel I can use to get the egg off my face? Man, Do I look bad right now. Still running at almost 13 hours and file is still growing.
0
dgerlerAuthor Commented:
The reindex ended up take 14 hour 40 mins for a table with 160 Mil rows and 5 indexes.

If I were to reindex it tonight, it should complete relative quick, right?
0
chapmandewCommented:
I would think it would be quicker...but as to how much quicker there is really no way to say.
0
dgerlerAuthor Commented:
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.