Post reply

Backup Plan Help / Maintenance Plan

  • January 2, 2007 at 4:28 pm

    #116747

    This is my first post to these forums and after spending all afternoon browsing, searching and reading I still have a few questions to my specific situation that haven't been answered.

    Our current maintenance plan for SQL.

    Optimizations (Running once a week on Sunday at 4am)

    - Reorganize data and index pages, change free space per page to 10%

    - Remove unused space from DB files, shrink database when it grows beyond 50mb, amount of free space to remain after shrink 10%

    Integrity (Running once a week on Sunday at 3am)

    - Check DB integrity, include indexes

    Complete Backup (Once a week on Sunday at 11pm)

    - Backup DB as part of MP to Disk

    Transaction Log Backup (Runs MTWTF every hour from 1am to 10pm)

    - Backup T-Log as part of MP to disk

    ******

    My questions are the following:

    • Our main DB is approx. 18gig and the log file is roughly the same size, is this typical?
    • Why does it not seem like the t-log is being truncated after being backed up?
    • We have about 10 DB's setup and all of the logs are about the same size as the MDF file. This just seems odd to me.

    I appreciate any insight I can get on this matter.

  • January 2, 2007 at 4:37 pm

    #680873

    Log file might have grown to the same size of data file or some one might have created with the same size...

    Is all 18GB log file is used? Is there any free space?

    You can shrink the file using dbcc shrinkfile command if you want to...

    Your maintenance job is running tlog backup Mon... to Friday why not Sat. and Sun?

     

    MohammedU
    Microsoft SQL Server MVP

  • January 2, 2007 at 4:48 pm

    #680875

    The original reason was because there were no transactions occurring on the weekend, this has since changed and I will update the MP accordingly.

    Another question, do the System DB's need to be backed up nightly, they are currently on a separate MP that only runs once a week as well.

    Thanks.

  • January 4, 2007 at 6:55 am

    #681186

    I find it best practice to backup every database every night.  Run the consistency checkers once a week, and reindex when you can.  I have several systems I can reindex nightly and several that are weekly.  I personally have never liked maintenance plans and have scripts and jobs that run all my maintenance and backup jobs.  There are a lot of good scripts in the scripting section.

    Tom

  • January 4, 2007 at 1:05 pm

    #681314

    If you are not adding/deleting logins, alerts, mp, dts packages, job ect... every day then weekly should be fine other wise it should be daily...

    I don't see any reason not to run daily because it takes few seconds...

    MohammedU
    Microsoft SQL Server MVP

  • January 6, 2007 at 11:19 am

    #681646

    Log file size depends on its initial set, number of transactions in given period, backup maintenance plan (if you would shrink your log file), and transaction log (database backup will not truncate your log file).

     

Viewing 6 posts - 1 through 5 (of 5 total)

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