SQL Server Performance Forum – Threads Archive

Maintenance Plan Best Practices

One of my [production servers has about 50 user databases. I have set up the maintenance plans as below:
Plan1:
Complete backup of all System and user databases with
the option, verify the integrity of the backup upon completion checked. This plan does not include optimizations, integrity check and transaction log backup
This plan is scheduled to run every night Plan2:
Only transaction log backup of all user databases with the option verify the integrity of the backup upon completion checked.
This plan is scheduled to run every hour
Plan3:
All System and user databases optimization and integrity check with following options:
Reorganize data and index pages (change free to 10%) Â¡ÃŒ
Check database integrity Â¨C include all indexes and attempt to repair minor problems This plan is scheduled to run once every Friday. All plans are executed by 3rd party product SQLLiteSpeed My questions:
1. Any suggestions on improving on this plan?
2. I am wary of the Integrity check. What if it is still running when I come on Monday morning?
3. Will it be better if I create a separate integrity check job for each database rather than having one single job with several steps?
4. What happens to a maintenance job with several job steps fails on last step?
5. This server has 3 logical drives (one each for data, log and backups) on a SAN. I feel having the log files for all databases on the same drive will slow down as it will not be a sequential access any more . Anythots on that?
6. If I create a hotbacku for the server with logshippong evry hour, will it cause any performance problems on the prod server?

Few thins I specify: First of all database maintenance plans are not good enough to take care of ceratin things like data/index optimization as it requires exclusive usage on tables, and when you’re running OLTP application with 24/7 method its not possible. I suggest to differentiate the small databases say < 2GB (in your case it depends on highest to lowest database size) and put all the small ones in one plan and big ones with individual plan. This is applicable to all the databases and perform system database backup seperately and ensure there is enough disk space on the server. http://www.sql-server-performance.com/ak_inside_sql_server_maintenance_plans.asp
http://www.sql-server-performance.com/sql_server_maintenance.asp .. for further information. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

]]>