locked
Maintenance Jobs Scheduling Problem RRS feed

  • Question

  • We are running below Jobs on SQL server.

     

    1. Weekly (Sunday) 12 AM - Full Backup

     

    2. Daily (Mon to Sat) 8 PM - Differential backup.

     

    3. Daily (Mon to Sun) 12 AM - Maintenance Plan (Rebuild Index, Reorganize index and Update statistics Tasks).

     

     

    Due to above schedule, We didn't find much difference between Full backup and Differential Backup (In size & Backup time).

     

    Please suggest what are the best timing schedule for above Jobs?

    If I change Maintenance plan (rebuild, reorganize and update stats) to weekly, is it effect on Performance?

    Wednesday, November 2, 2016 11:09 AM

Answers

  • 3. Daily (Mon to Sun) 12 AM - Maintenance Plan (Rebuild Index, Reorganize index and Update statistics Tasks).

    You do all the points in one Job and I guess may be directly after the full backup? Then it's not a big surprise ...

    When you run a rebuild index, there is absoultely no need to run an additional reorganize and an update statistics; that's all done by the reorganize.

    A differential backups saves all changed data pages since the last full backup and an index reorganize will Change a lot of data pages; that's the reason why your differential backup is that large.

    You should better re-thing your maintenance plan.


    Olaf Helper

    [ Blog] [ Xing] [ MVP]


    Wednesday, November 2, 2016 11:37 AM
    Answerer
  • Here's an alternative:

    Weekly rebuild, and full backup in that order

    Daily (except for above day) diff backups

    Then of course consider if you want to do log backups, rather use Ola Hallengre's scripts instead of the maint plans, etc. Standard considerations for maintenance plans. But above addresses your immediate concerns.


    Tibor Karaszi, SQL Server MVP (Web Blog)

    • Marked as answer by srinivas_ch Thursday, November 24, 2016 9:57 AM
    Friday, November 4, 2016 8:41 AM

All replies

  • 3. Daily (Mon to Sun) 12 AM - Maintenance Plan (Rebuild Index, Reorganize index and Update statistics Tasks).

    You do all the points in one Job and I guess may be directly after the full backup? Then it's not a big surprise ...

    When you run a rebuild index, there is absoultely no need to run an additional reorganize and an update statistics; that's all done by the reorganize.

    A differential backups saves all changed data pages since the last full backup and an index reorganize will Change a lot of data pages; that's the reason why your differential backup is that large.

    You should better re-thing your maintenance plan.


    Olaf Helper

    [ Blog] [ Xing] [ MVP]


    Wednesday, November 2, 2016 11:37 AM
    Answerer
  • Here's an alternative:

    Weekly rebuild, and full backup in that order

    Daily (except for above day) diff backups

    Then of course consider if you want to do log backups, rather use Ola Hallengre's scripts instead of the maint plans, etc. Standard considerations for maintenance plans. But above addresses your immediate concerns.


    Tibor Karaszi, SQL Server MVP (Web Blog)

    • Marked as answer by srinivas_ch Thursday, November 24, 2016 9:57 AM
    Friday, November 4, 2016 8:41 AM