locked
SQL server maintenance task RRS feed

  • Question

  • Hi friends,

    I have just installed a fresh SQL server 2008 R2 two node Active-passive cluster.

    Now databases has migrated to this sql server. having 11 databases & in which 5 databases are more than 500 GB.

    Now I want to configured maintenance task for this, as i am new I don't know how to configure it & what task should i mentioned it?

    Please help me.

    Monday, June 11, 2012 9:54 AM

Answers

  • You should be backing everything up but you need to understand your RPO and RTO first so you can decide what your recovery requirements are. You should also be checking for corruption, rebuilding/reorganising indexes and updating statistics. You need to understand your particular requirements in order to decide what your maintenance needs to include.

    Sean Massey | Consultant, iUNITE

    Feel free to contact me through My Blog, Twitter or Hire Me.
    Please click the Mark as Answer or Vote As Helpful button if a post solves your problem or is helpful!

    • Marked as answer by Maggie Luo Sunday, July 8, 2012 5:19 PM
    Tuesday, July 3, 2012 7:39 AM

All replies

  • Hi,

    You don't need to have just one maintenance task that all databases use. You need to be backing up, running consistency checks, rebuilding or reorganizing indexes and keeping stats up to date.

    I would suggest you look at using Ola Hallengren's maintenance scripts.

    http://ola.hallengren.com/


    Sean Massey | Consultant, iUNITE

    Feel free to contact me through My Blog, Twitter or Hire Me.
    Please click the Mark as Answer or Vote As Helpful button if a post solves your problem or is helpful!

    Monday, June 11, 2012 10:45 AM
  • can you tell me using maintenance plan how can i do it?

    Monday, June 18, 2012 5:34 AM
  • Hi Mayur,

    Maintenance plans create a workflow of the tasks required to make sure that your database is optimized, regularly backed up, and free of inconsistencies. The Maintenance Plan Wizard also creates core maintenance plans, but creating plans manually gives you much more flexibility.

    Refer to Maintenance Plans: http://msdn.microsoft.com/en-us/library/ms187658.aspx.


    Thanks,
    Maggie


    Please remember to mark the replies as answers if they help and unmark them if they provide no help. This can be beneficial to other community members reading the thread.

    Monday, June 18, 2012 5:00 PM
  • Hey friends,

    My problem is not solved, could you please tell me on weekly basis what my maintenance task should include.

    I have 15 databases having more than 80 GB size.

    Tuesday, July 3, 2012 2:24 AM
  • Hi Mayur,

    Using maintenance plan we can take backup, rebuild index and reorganise ... and so on..

    to take database backup using manitenance plan , follow this.........

    http://msdn.microsoft.com/en-us/library/ms177266(v=sql.105).aspx


    padala


    • Edited by Padala Tuesday, July 3, 2012 6:18 AM
    Tuesday, July 3, 2012 6:17 AM
  • You should be backing everything up but you need to understand your RPO and RTO first so you can decide what your recovery requirements are. You should also be checking for corruption, rebuilding/reorganising indexes and updating statistics. You need to understand your particular requirements in order to decide what your maintenance needs to include.

    Sean Massey | Consultant, iUNITE

    Feel free to contact me through My Blog, Twitter or Hire Me.
    Please click the Mark as Answer or Vote As Helpful button if a post solves your problem or is helpful!

    • Marked as answer by Maggie Luo Sunday, July 8, 2012 5:19 PM
    Tuesday, July 3, 2012 7:39 AM