Rebuild of indexes

[Hokie97VT]

I have an software developer who has built a custom web application with SQL for the DB. He has never ran a rebuid of the indexes and we are just now running it because a user has complained that they have had no date since the last 3 weeks.

How often to indexes need to rebuilt?

 

[Hokie97VT]

As well as how long should rebuilds take.

Thanks.

 

[markros]

See if this blog may be of help (strange URL, BTW)

http://blogs.lessthandot.com/index.php/DataMgmt/DataDesign/title-5

 

[gmmastros]

Rebuilding the indexes does NOT correct data problems. If your user is complaining about missing data, then the whole DB is corrupt, or the data is simply not there.

To check for a corrupt database:

dbcc checkdb

If that displays any errors, and you don't understand the error messages, copy/paste them here.

Rebuilding indexes CAN take a while for larger DB's. It all depends on the size of your database. For me.... my DB is about 500 megs, and it takes about 4 minutes. But.... if you have more indexes (even on a smaller db), it could take longer.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Hokie97VT]

I do not have the size of the DB at this time, but my engineer is stating that it is takinng 3.5 days.

Should it take this long?

 

[Hokie97VT]

I am new to the troubleshooting part. Where do I go and trype that in to see if the DB is corrupt?

 

[Hokie97VT]

Does the system provide a running statistic of how far along the rebuild of the indexes it is. My developer is stating that he has no clue how much longer it is going to take and that is unacceptable. I need to provide a resolution time.

Should rebuilds of indexes be performed on a schedule every night or once a week or is it something that normally should never be needed?

Any advice would be appreciated.

 

[gmmastros]

If it's been running for 3.5 days, something is definitely wrong. I don't think there are any running statistics on the progress (but I could be wrong).

Rebuilding indexes is a big question. For my 500 meg database, I rebuild once per week, and my customers are completely satisfied. I know others that rebuild nightly, and then others that do incremental build every couple hours. Incremental builds require the Enterprise version.

My suggestion would be to make sure you have a good backup of the database first. Then, kill the reindex process. Put the database in to single user mode. Check the status (using DBCC CheckDB), then.... if there are no errors, rebuild the indexes. Finally, put the database is multi-user mode.

Seriously, though.... I wouldn't do ANY of this unless you can verify that there is a good backup, just in case something incredibly bad has happened.

I have friends of mine with terrabytes of data and rebuilding the indexes doesn't take anywhere near that long.

To type in commands (like "DBCC CheckDB"), you'll want to use "SQL Server Management Studio". This should already be installed on the server computer hosting the database.


-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[markros]

I've discussed this with the author of the blog I pointed you earlier.

Here is some of what he said on this topic

---------------------------------------------------------
I never wrote one pretty much beause its always talked about so much. Denis did write one though

http://blogs.lessthandot.com/index.php/DataMgmt/DataDesign/finding-fragmentation-of-an-index-and-fi

at the end of that blog Denis linked to Michelle (sqlfool) script to defrag. it's the best I've seen really. it I even run it