Results 1 to 6 of 6

Thread: LDF files

  1. #1
    Join Date
    Mar 2010
    Posts
    2

    Unanswered: LDF files

    Hello all of you, thanks for the time spent to read my question.
    i am experiencing some dificultes with LDF files in my sqlserver 2005.
    the files grow sometimes to 50 GB, and in this cases i detach database, remove the transaction log to backup disk, and next step attach the database again and and create the a newer log file. Is this practice correct ?
    can i do this automaically in a backup plan ?

    thanks

    Vasco Ribeiro

  2. #2
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Yeah, I heard about this method. I would recommend to stop this practice, and replace it with the following steps (I assume that your database is in FULL recovery mode):

    - Set the database to SIMPLE recovery;
    - Shrink the log;
    - Set the database back to FULL recovery;
    - Perform FULL database backup;
    - Create transaction log backup scheduled task, and schedule it to run every hour;
    - Monitor the size of resulting backup devices, and if the size of each file on average does not exceed 50MB, modify the schedule to 2 hour frequency.

    If the assumption about FULL recovery mode is incorrect, then you need to research the cause of growth, and maybe post the results here, so that we can address it adequately.
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  3. #3
    Join Date
    Mar 2010
    Posts
    2
    but these steps work like a maintenance plan ?

  4. #4
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    You can (and probably should) set up the log backups in a maintenance plan.

  5. #5
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    ...and I hope the OP completely ignores the above suggestion, with the exception of the warning in the last sentence, which refutes the suggestion in its entirety.
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  6. #6
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by rdjabarov View Post
    ...and I hope the OP completely ignores the above suggestion, with the exception of the warning in the last sentence, which refutes the suggestion in its entirety.
    I'll agree with that... The posting appears to be sig-spamming, just spouting what seems like drivel to me so I neutered the sig to see what would happen.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •