How often shoud we run reorganize index and update statistics...

We have a 20 GB database and reorganize indexes and update statistics maintainance takes about 4 hours and the log files grows out of control what is a serious problem since it can not be truncated (database mirroring).

Ivan

First of all if your log file is growing it just means that it is not properly sized for your environment. In fact, have a proper sized log file will cut down the reorganize time.

If you have not changed anything to the autostats setting your statistics will be updated after enough records have changed for SQL Server to consider it usefull to update them.

Are you talking about a reorganize or a rebuild of your indexes? You should rebuild your indexes once in a while and the frequency entirely depends on the fillfactor and the amount of data that is changed between your rebuilds.

Depending on how much time it takes I would say reindex as frequently as you can afford to (daily/weekly?).

WesleyB

Visit my SQL Server weblog @. http://dis4ea.blogspot.com

|||

I think we have an equivalent question : How often do we use indexes or querying database ?

What are the most importants queries used by users ? You can answer at this question.

If you can do a top of this queries you can reorganize/rebuild the indexes used by first queries of the top. Later or rarely another indexes.

From other part don't farget "avg_fragmentation_in_percent value " that implies if you do reorganize or rebuild of indexes (see Books Online).

You can do this thing using a job (or Database Maintenance Plan) nightly when the people sleep.

sql