Tutorial :SQL Server 2005, Maintenance Plan suggestion


I need some advice on SQL Server 2005 maintenance plan, okay here some question:

  1. What task(s) is/are suitable for daily maintenance and what for weekly/monthly maintenance
  2. Do database need to be offline while in some task, example : reorganize/rebuild index, Shrinking database, etc... (since we need to keep 90% uptime)
  3. How long can check database integrity, reorganize/rebuild index, cleanup history be?
  4. Should we do both reorganize and rebuild index?
  5. Do we need to update statistic after reorganize index? Since rebuild index will auto update statistic

In our case, data is inputed every 1 minute (merely 200 records per minute) 24 hours, 7 days a week.

Can someone suggest me what Maintenance Plan that good for this database?



Maintenance plans really depend on your database processes. Since the data is inputted every minute, do you have any rollup and etl processes running?

The most important process that I can tell you is the daily backup (both tape and disk)of your data and transaction logs.

Check for any slow running queries using query plan analyzer and you may need to re-index some of your tables either daily or weekly depending on your needs. You can do online re-indexing in SQL Server 2005 enterprise edition, which means you do not need to be offline.

Have a good process by creating a maintenance plan and automate as much as possible by creating scheduled jobs.


Two Words: Disaster Recovery

The best plan is one which you have tested.

Plan for a disaster recovery and see how much you can restore your existing system from this plan. Note any issues and find ways of resolving them, then re-run the disaster recovery. You will find this will help you make decisions and prioritize requirements.

It is best to do this with both an O/S restore as well as an SQL server restore.

Also some advice: Setup an O/S scheduled task to do a file system copy of the master, model, mssqlsystemresource databases. This will save you grief and having to run the SQL server in single-user mode to try and restore your master db from a backup.

Its all well and good having backups, but if you never test recovery, then your backups are worthless.


To maintain performance and ensure database consistency, I typically run the following tasks each and every night:

1) Backing up a database (Typically this is a FULL backup. However if the database is very large then a FULL backup is run once per week {on the weekend} and an incremental or differential each weekday)

2) Rebuild all indexes (This automatically reorganizes all indexes as well, so the reorganize step is not needed.)

3) Updating database statistics (Only Column statistics are needed since the other statistics are automatically updated by the Index Rebuild which ran earlier)

4) Verifying database integrity (This is the most important step as it could get corrupted by almost anything and still run just fine for a while, meanwhile all the data is getting more and more corrupted.)

5) Notify operator via e-mail of success (You must send both success and failures. IF you only send failures then there is a chance the server might have another issue preventing the sending of the failure email and then they would never know.)

6) Cleaning files (Depending upon the drive space, you will need to remove old backups. I try to keep at least a few weeks worth if there is room, but as the database grows will need to check this and possibly reduce this to as low as one or two full backups.)

7) Cleanup history (The Maintenance plan log history is not very large so you can keep a long history of these without worrying about space too much. It is sometimes helpful when you come across a database that has been failing for a long time to see when it started failing and use that to point out to the people monitoring it that it has not been properly monitored for success.)

NOTES: Be sure to copy the backups off the main database server and store them offsite.

Note:If u also have question or solution just comment us below or mail us on toontricks1994@gmail.com
Next Post »