Post reply

Need Best Maintenance plan

  • March 4, 2011 at 3:30 pm

    #233003

    HI Everey one:

    What the Best Maintenance plan?

    1- CheckDB

    2- Shrink

    3- Rebuild indexes

    OR

    1- Rebuild indexes

    2- CheckDB

    3- Shrink

    Or another

    Thanks for every one Answer me

  • March 4, 2011 at 3:57 pm

    #1294919

    Your first ordered list is better than the second.

    You could also check out the scripts provided by Ola here[/url].

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • March 4, 2011 at 4:06 pm

    #1294921

    First of all a good maintenance plan should include backup/recovery. Are your using Simple Recovery or Full Recovery Model?

    Second what is your apparent obsession with SHRINKING either the database or log files? They are only going to grow again, and Log file size can be somewhat controlled by using the Full Recovery Model, with periodic log file backups, so as to allow a recovery of the database up to a point in time.

    Rebuilding indexes, do you intend to rebuild all indexes on all tables or only those which show Index fragmentation above a certain percentage?

    These questions are but a few of those you should consider when devising a maintenance plan.

    If everything seems to be going well, you have obviously overlooked something.

    Ron

    Please help us, help you -before posting a question please read[/url]
    Before posting a performance problem please read[/url]

  • March 4, 2011 at 4:20 pm

    #1294923

    Thanks for your interest

    I have Full Recover

    I execute this plan but i don't know if this order is a good or no:

    1- CheckDB

    2- Reorganize Indexes For everey table

    3- REBUILD Indexes For everey table

    4- UPDATE STATISTICS

    5- SHRINKDATABASE

    6- SHRINKFILE

    7- Full BackUp Everey

  • March 4, 2011 at 4:35 pm

    #1294925

    Do Not Shrink Your Database!!!!

    Doing so after a rebuild is a total and complete waste of time. The shrink undoes what the rebuild does. Do Not shrink regularly. If used at all it should be a once off operation.

    Rebuild after reorganise is stupid. Reorganise shuffles the index pages into order. Rebuild then drops the index entirely and recreates it. Total waste of effort.

    Basically that list you have there is the worst possible maint plan you could have.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • March 4, 2011 at 5:24 pm

    #1294930

    Thanks for your great advise

    So:

    Can you help to put full maintenance plan

    because i'am a VB.net Programer and i don't have any experience

    I create tables views and Read and write but i don't found friend to help me , So

    If You Help me then

    Thanks

    else

    Thanks

    End if

  • March 4, 2011 at 6:34 pm

    #1294936

    Maintenance plan 1. CheckDB and full DB backup. Nothing else.

    Then a job to do index rebuilds. See the link to scripts by someone a few posts back.

    Maintenance plan 2: log backups. frequency dependent on how much data loss you can tolerate

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • March 4, 2011 at 6:51 pm

    #1294938

    Note that you can stick the index rebuild in a maintenance plan by calling it from the Execute SQL task, or you can just put it in a job step.

  • March 4, 2011 at 7:54 pm

    #1294944

    Big greeting to you

    You really help me

    but in my mind i say:

    This is courageous man or has high experience ,

    but i say finally the second idea.

    any way thanks very much.

  • March 10, 2011 at 1:35 am

    #1296921

    Is it always necessary to rebuild indexes or once can just do DBCC and Backup and recovery only?

    What tool or script can i use to detect whether tables need reindexing?

    With regards to DBCC, is it also necessary to have it run on daily basis or not?Why i ask is because my SQL enviroment usuall has one schedule which is a Daily full backup and cleanup task. No DBCC job.

    Please advise.

  • March 10, 2011 at 1:44 am

    #1296923

    Which DBCC? There are over 20 such commands.

    Yes, it is usually necessary to rebuild indexes from time to time and do other database maintenance.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • March 10, 2011 at 2:20 am

    #1296930

    DBCC CheckDB

  • March 10, 2011 at 2:23 am

    #1296934

    You need to rebuild indexes based on fragmentation. You need to run checkDB on a regular basis. Maybe not daily, but at least weekly.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • March 10, 2011 at 2:39 am

    #1296948

    Thanks!

  • March 12, 2011 at 5:44 pm

    #1298057

    The shrink operation, in my opinion, should be removed from the maintenance plan configuration since there is NO good reason for why it should ever be executed regularly. Pretend that option doesn't exist.

    Still not sure why MS leaves it in there.... Maybe more calls for support = more $$ when the shrink operation kills performance? :w00t:

    Steve

Viewing 15 posts - 1 through 15 (of 22 total)

You must be logged in to reply to this topic. Login to reply