I have a stored procedure that maybe could be useful for you. It is using the DMV

sys.dm_db_index_physical_stats to dynamically rebuild / reorganize indexes based on the fragmentation levels.

http://ola.hallengren.com/sql-server-index-and-statistics-maintenance.html

Ola Hallengren

http://ola.hallengren.com

I just would like to check one thing. As i understand it you're using Maintenance Plans. In the Maintenance Plan Rebuild Index Task you have a setting called 'Change free...

Yes, this will work fine.

It is only the database that you are creating the objects in, that has to be in compatibility level 90. All other databases can be in...

>After reading some of the posts, the maintenance plan is really too generic and doesn't give you much flexibility in hindsight.

I have a stored procedure that maybe could be useful...

I think that rebuiling the indexes with a 100% fillfactor (or 0% free space if you use the Maintenance Plans), backing up the log and then shrinking the database, will...

> ... so I temporarily setup a maintenance plan using the Rebuild Database task using an 80% fill factor.

I'm thinking about how you have configured this. In the Maintenance Plan...

>We have separate maintenance plans for Dbs in simple mode and Dbs in full mode, so when a new db is added to the server we might forget to add...

Not all indexes can be rebuilt online. Clustered indexes on tables with LOB columns cannot be rebuilt online. Non clustered indexes with LOB columns in the index definition cannot be...

I think that there is a known bug in the Maintenance Plans related to this.

http://support.microsoft.com/kb/938085

It looks like it's fixed in cumulative update package 2.

Ola Hallengren

http://ola.hallengren.com

With Event Notifications you could define all DDL events on the server scope.

http://msdn.microsoft.com/en-us/library/ms180824.aspx

Ola Hallengren

http://ola.hallengren.com

I have done some successful migrations using full and differential backups. You will only need downtime the time it takes to do the differential backup and restore.

I think that it...

You could use the dynamic management view sys.dm_exec_requests for this. It has a column called percent_complete.

http://msdn.microsoft.com/en-us/library/ms177648.aspx

Ola Hallengren

http://ola.hallengren.com

I have a stored procedure that could help you with this. The stored procedure does index rebuild online or offline, index reorganization, statistics update, index reorganization and statistics update or...

> ... or Example D (I think) of sys.dm_db_index_physical_stats - and only remove fragmentation in indexes where it's going to be worthwhile

I have a stored procedure that you could also...

Yes, I think that will work fine.