• suedunham2 (11/24/2014)


    Thank you for that prompt reply - it looks like there is no way of reducing the size of the first Monday tran log dump unless I change the database from full to simple recovery mode for the duration of the maint plan. Is that something you would recommend?

    Thanks also for the helpful PS! I did not set up the maintenance plan and can see that whoever did has not made good choices. Is it right that if I set it to rebuild that would be enough (because it renders reorganise and upd stats redundant)? The largest database that we are managing is Sharepoint, I have heard that the tables are prone to fragmentation - is rebuild the best choice here too?

    To emphasize what Gail has already said, don't change the recovery model. Instead, start doing transaction log backups on the weekends just like during the week.

    Also, the maintenance plan rebuilds rebuild everything and it's all logged. That will make your log files much larger than necessary which will also make a restore take much longer if it ever comes to that. Use Ola's script for that as Gail suggested. It will figure out if an index needs to be rebuilt, reorganized, or just left alone. It'll do the same with statistics.

    Just in case you don't know, make sure that you're backups aren't going to the same disks as the data because if the data and the backups live on the same drives and something really bad happens to the disk system, you won't have the data or the backups to get back in business quickly.

    Make sure that someone is taking tape backups on the disk backups. 😉

    You also need to make a recovery plan which includes backups. If you have the space for it (and disk space is relatively cheap so you should get the space for it if it's not already available), take a full backup every night and log file backups every 15 to 30 minutes, certainly no longer than once an hour. Then, practice doing a restore to a test box at least once a month. You'll thank your lucky stars that you know how to quickly and easily do such a thing if something ever goes wrong and you need to do a restore for real.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)