Results 1 to 14 of 14
  1. #1
    Join Date
    Jul 2011
    Location
    Nasik, Maharastra
    Posts
    34

    Unanswered: Log file size(.ldf) is 4 time greater then data file(.mdf)

    Dear all,
    Greetings!!

    I have a database whose log file size is 4 time greater then data file size, and its continuously growing day by day. Recently face limited disk related issue.

    Is there any way to truncate log file???
    What is impact on db if i truncate log file???
    Is there any way to prevent this file continuously growing???

    Valuable suggestions always appreciated.

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Are you not taking log backups?

  3. #3
    Join Date
    Jul 2011
    Location
    Nasik, Maharastra
    Posts
    34
    I am taking only every day full backups.
    No transaction log backups.

  4. #4
    Join Date
    Jul 2011
    Location
    Nasik, Maharastra
    Posts
    34
    I have created one maintenance plan to take transaction log backup. (one time activity).
    After successfully execution transaction log backup maintenance plan, i was expecting log file size reduce. But data size and log size is same before and after taking backup of transaction log files.

    My data size is 4GB
    Log size: 29GB
    transaction log backup size 6GB

    Please suggest something.

  5. #5
    Join Date
    Jul 2011
    Location
    Nasik, Maharastra
    Posts
    34
    My question is, Is there any way to reduce log file size???

  6. #6
    Join Date
    Feb 2013
    Location
    perth australia
    Posts
    24
    you should have a transaction log backup as part of your scheduled backup to help remove committed transactions from your transaction log ( not just a once off ).

    run dbcc sqlperf(logspace) to confirm your transaction log file is mostly empty.

    If it is you can do a once off dbcc shrinkfile. You can do this graphically thru mgmt studio to release unused space.

    Thong

  7. #7
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Right-click on your database.
    Select Tasks/Shrink/Files
    Choose
    ---File type: Log
    ---Shrink action: Release unused space

    The dialog box will show you the currently allocated space, as well as the Available free space.
    Click OK.

    Going through the steps above again, you should see that the allocated space and available free space have decreased. If you don't see any change, let us know.

    You should not have to perform this task regularly, if you schedule automated log backups. (And there's really no point in having your database in full recovery mode if you aren't doing regular log backups.)
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  8. #8
    Join Date
    Aug 2004
    Location
    Dallas, Texas
    Posts
    831
    Quote Originally Posted by jignesh_foryou View Post
    I have created one maintenance plan to take transaction log backup. (one time activity).
    After successfully execution transaction log backup maintenance plan, i was expecting log file size reduce. But data size and log size is same before and after taking backup of transaction log files.

    My data size is 4GB
    Log size: 29GB
    transaction log backup size 6GB

    Please suggest something.
    Try running this code snippet and check to see if the log reuse is waiting for some event to complete:

    Code:
    SELECT name, log_reuse_wait_desc FROM sys.databases
    I had a similar problem recently and discovered that an uncommitted replication command was keeping the transaction log from truncating. I committed the replication transactions, did a full backup including the log and this reduced the log file to what I would have expected. What is your recovery model? Full, simple or bulk?

  9. #9
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    A BIG clarification is needed here. "Reducing the size of your transaction log" can include simply emptying out the active records. It does not necessarily mean "automatically reducing the footprint of the file on disk". In fact, there are no files that are automatically shrunk in SQL Server. This is because shrinking a file requires a very technical thing called "work".

  10. #10
    Join Date
    Aug 2004
    Location
    Dallas, Texas
    Posts
    831
    Quote Originally Posted by MCrowley View Post
    A BIG clarification is needed here. "Reducing the size of your transaction log" can include simply emptying out the active records. It does not necessarily mean "automatically reducing the footprint of the file on disk". In fact, there are no files that are automatically shrunk in SQL Server. This is because shrinking a file requires a very technical thing called "work".
    Work? Novel concept.

  11. #11
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    I'm against it myself, but what can you do? It seems to be a fad.

  12. #12
    Join Date
    Aug 2004
    Location
    Dallas, Texas
    Posts
    831
    Quote Originally Posted by MCrowley View Post
    I'm against it myself, but what can you do? It seems to be a fad.
    I hired a DBA to help me so I can concentrate on more important tasks like planning and reporting. Now he's doing the support and deployments which isn't really work but keeps the developers happy.

  13. #13
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    ...like an airline hiring pilots to fly the plane, so that hostesses can concentrate on the important task of serving drinks and meals to passengers, right?
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  14. #14
    Join Date
    Aug 2004
    Location
    Dallas, Texas
    Posts
    831
    Quote Originally Posted by blindman View Post
    ...like an airline hiring pilots to fly the plane, so that hostesses can concentrate on the important task of serving drinks and meals to passengers, right?
    Lol! Not quite so here. I am in the process of repairing the planes so he can fly them without crashing and burning every other day.

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
  •