SQL 2005 Maint Plan Recommedations

[cramlow]

Hi experts,

I am trying to set up a best practice maintenance plan for my sql 2005 servers. I know I need to have a full recovery model and perform a daily full and an hourly log backup, but after that I am not sure exactly what else should be automated. If you recommend that other things should be in a "best practice" plan, like indexing and database shrinking, can you please tell me why I want to do this so I can better understand my sql maintenance plans. Also, I need to know so I can reiterate it back to my supervisors.

Thanks!

[rmiao]

You should do db integrity check (ensure db healthy), reindex (improve query performance and defrag table), plan and backup files clean up. Don't shrink db, it's performance killer.

[cramlow]

Isn't db shrinking important? Shouldn't it be done at least occasionally? What does it do for the db?

[skhanal]

You should try to size the database when you first create it, this way you do not pay the price of auto extend during application use. It not only slows down the application during extension, it causes file level fragmentation as the new segments added to the data file may not be next to the existing segment.

Shrinking causes data to be moved to the beginning of files and it may cause logical fragmentation.

So shrink it only if you really need to, for example when one database unnecessarily expanded eating up all the disk space.

[cramlow]

Thanks,

You guys have made my week!