SQL Server 2005 Shrink and Rebuild indexes

Question

Hello,

We have a weekly maintenance plan to shrink all user databases and rebuild their indexes. This has working fine until we created a read-only database, now each time the plan runs it fails when it starts processing this database due to its read only state.

As far as I can see we have two options remove the read only flag from the database, this is possible but as the database is only updated once a quarter it makes sense from a performance point of view to make use of the read-only feature. Or manually select the database that the plan should run for i.e. all the users databases apart from the read only one, this then requires people to remember to add any new databases into the plan.

Does anyone have any suggestions of a better way of doing this?

Thanks

Neil

Answer 1

why are you shrinking the database in the first place? also there's no need to maintain read opnly db's like that.

Answer 2

I'd remove the read only flag if you don't want to customise the maint plan.

Why are you shrinking DBs too? If the database grows to a given size, then this is probably it's natural current size.

Also remember that an index rebuild (rule of thumb) require free space of 120% of target table size. Eg 500 MB table needs 600 MB free space.

It's pointless to shrink then rebuild... and you'll have horrendous file fragmentation too

Answer 3

I suppose could modify the maintenance plan to start with a 'Execute T-SQL Statement' step, which removes the readonly flag (ALTER DATABASE database-name SET READ_WRITE) and add a final step to reset it: ALTER DATABASE database-name SET READ_ONLY