Solved

How to determine the progress DBREINDEX

Posted on 2008-10-28
12
1,011 Views
Last Modified: 2010-04-21
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?
0
Comment
Question by:dgerler
  • 7
  • 5
12 Comments
 
LVL 60

Expert Comment

by:chapmandew
ID: 22822338
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
 

Author Comment

by:dgerler
ID: 22822419
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
 
LVL 60

Expert Comment

by:chapmandew
ID: 22822451
No, the problem is that the DMVs only report info for 2005 instances...they wouldn't have info for the 2000 machine....
0
 

Author Comment

by:dgerler
ID: 22822622
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
 

Author Comment

by:dgerler
ID: 22822630
I mean that 2/3 of the data in the file is in that table with 1/3 being in other tables.
0
 
LVL 60

Expert Comment

by:chapmandew
ID: 22822642
No way for me to be able to tell that....sorry.
0
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 

Author Comment

by:dgerler
ID: 22822668
I appreciate the information you have provided.

Thank You.
0
 
LVL 60

Accepted Solution

by:
chapmandew earned 500 total points
ID: 22822693
Welcome....it would be a neat feature for them to add the progress on it....it just doesn't exist right now.  Sorry.
0
 

Author Closing Comment

by:dgerler
ID: 31510752
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
 

Author Comment

by:dgerler
ID: 22827510
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
 
LVL 60

Expert Comment

by:chapmandew
ID: 22827832
I would think it would be quicker...but as to how much quicker there is really no way to say.
0
 

Author Comment

by:dgerler
ID: 22830845
0

Featured Post

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
set default date format in mssql to mm/dd/yyyy 22 69
SQL Query stumper 3 37
Get Duration of last Status Update 4 32
SQL2014 execution plan differences 10 16
Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Deploying a Microsoft Access application in a Citrix environment is not difficult but takes a few steps. However, Citrix system people are often of little help, as they typically know next to nothing about Access. The script provided here will take …
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…

864 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now