Heavily fragmented Indexes - SQL Server 2005

Question

I recently inherited a poorly maintained production database with heavily fragmented indexes (most of the indexes with more than 80% fragmented). I requested downtime with my manager to perform Index rebuild, but unfortunately downtime is not allowed at the moment. If online Index reorganize too is not option, can I do the following?

1. Restore a fresh production copy to a test instance
2. Rebuild Indexes, update statistics
3. Overwrite the prod database from test instance
4. Apply transaction logs to get the database.

Though the above method too requires downtime, but its relatively less. I wanted to know whether one can do this or I am just being stupid :) Please advise

RK

Answer 1

SQL Server 2005 has online index rebuilds (that is, non-blocking).

Otherwise, it's never offline (that is, database or server off line) but does have an exclusive locks on the table/index being rebuilt.

Answer 2

If most of the application's access is via seeks, then the fragmentation is not a problem. Otherwise I'd try to find some time in which the index rebuild will have minimal effect, and do it via a scheduled job. Surely the application isn't running 24/7/365 with poor maintenance, without the company expecting some problem to occur. (Do they change the oil on their cars?)

As far as your 4 step solution, copying the table to another database, rebuilding the index, and copying it back won't accomplish anything more than just rebuilding the existing index. On copying it back the index is rebuilt anyway, so just try and schedule a couple of tables at a time, until you get everything done.

Good luck.