• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 367
  • Last Modified:

Scheduling SQL Maintenance Tasks

Hi Experts,

I am wanting to perform regular maintenance on our new SQL server but am unsure as to what tasks are beneficial, how often to run them & how to schedule them so they are not manual tasks.

Any feedback is welcomed, look forward to reading your comments.
1 Solution
Patrick BogersDatacenter platform engineer LindowsCommented:

SQL server doesnt require much maintenance, tasks to schedule are more user defined like backup to offsite locations.
isdd2000Author Commented:
Hi Patrick,

So you don't recommend indexing, checking for corrupt entries or any of the other things Microsoft recommend?
Maintenance is a regular task and depends on various factors like the amount of transactions (DMLs) happening at DB end, amount of load on sql server etc. As this is an open end question, here are some pointers that will help you:
1. DB corruption - To avoid this, DBCC CHECKDB needs to be executed every week or fortnight based on the load and the output needs to be checked for any errors
If errors are being thrown, then they should be repaired accordingly (there are MS articles for the same in technet/msdn)
2. Index defrag/rebuild activity - This needs to be done regularly during less load times. Defrag needs to be done when the fragmentation is not too high, where as rebuild should be run when the fragmentation is high
3. Log backups - Log backups need to be run at regular intervals onto disk or tape
4. Statistics - Statistics need to be updated at regular intervals to ensure that the SQL Server is aware of what plan needs to be used and help it to fetch the data faster and easier

You can find more details in the below article:


Hope this helps!
Patrick BogersDatacenter platform engineer LindowsCommented:
Hi again,

I do recommend indexing and checking for corrupt entries, i just dont recommend it to be done in a scheduled task. It is the job for a DBA to have this hands/eyes on if you ask me.

We have scheduled tasks in place for creating backups, moving them to offsite location (where we have other scheduled tasks to import it) so we are 100% redundant and failover proof.

Please go through this link how to configure maintenance plan:


Also check below suggestions of Best Practice Strategy of backing up your databases:

Best Practices Strategy:

Full Backup: Daily
Differential backup: Every 1 hour
Transaction log backup: Every 5 or 10 mins

If you backup as mentioned above you will have transaction log backup size smaller.

As MS recommends perform backup of your system databases daily: master model and msdb
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

SMB Security Just Got a Layer Stronger

WatchGuard acquires Percipient Networks to extend protection to the DNS layer, further increasing the value of Total Security Suite.  Learn more about what this means for you and how you can improve your security with WatchGuard today!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now