Results 1 to 5 of 5
  1. #1
    Join Date
    Jul 2004
    Posts
    268

    Unanswered: .mdf and .ldf files

    Hi All,

    One of my databases has .ldf file that is bigger in size than .mdf file. Is it a problem? If yes, what can cause this problem? And how to fix it? I looked at all the other ones and the .ldf files are smaller than .mdf files. Thanks.

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    You probably forgot to change the Recovery Model from FULL to SIMPLE. After you've fixed that, dump the transaction log. From Query Analyzer

    Code:
    ALTER DATABASE myDb SET RECOVERY SIMPLE
    GO
    BACKUP LOG myDb WITH TRUNCATE_ONLY
    Be sure to change myDb to your database name.

    -PatP

  3. #3
    Join Date
    Jul 2004
    Posts
    268
    Thank you.

  4. #4
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    ...Except doing so will not change the size of the log device.

    Right-click on the database and see if you have any LOG (and FULL for that matter) backups done. After confirming that you have, determine what is the business requirement for database recoverability after a failure (1 hour, 6 hours, 24 hours, or "I don't care"), determine the volume of activity against the database (you can use perfmon Transactions/sec counter), and set up a transaction log dump job. Set up an alert to notify you when the space used on the log device exceeds the full size of the data device, and run for a day under this set of settings. Only then you can intelligently say what the actual size of the log should be, and can safely shrink the file to that size.
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  5. #5
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by rdjabarov
    ...Except doing so will not change the size of the log device.
    Hmmm... Maybe I mis-read the description of the TRUNCATE_ONLY option in the BOL for BACKUP LOG, but it sure looks like it will reduce the size of the device. Empirically, it works as described in BOL for me.

    I will agree with you (rdjabarov) that a DBA should not willy-nilly truncate the log or change the logging options. This is definitely something that should be considered before you just blindly hack away.

    -PatP

Posting Permissions

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