Results 1 to 11 of 11

Thread: Huge LOG File

  1. #1
    Join Date
    May 2003
    Location
    Pinoy in NJ
    Posts
    104

    Unanswered: Huge LOG File

    Our SQL Server run out of space today. Normally, I would have approx. 2 GB for my MDF file and less than 500K for my LDF file (checking backups).

    Today, I found out that the LDF file is 58 GB. I'm not sure how that happened and it ate up all the server's disk space.

    What do I need to check? How do I make the LDF file back to a normal size? Should I restrict it?

    TIA.

  2. #2
    Join Date
    May 2003
    Location
    Pinoy in NJ
    Posts
    104
    I run a DBCC SHRINKFILE but to no avail. When I use the Enterprise Manager GUI, it says I can only shrink the file up to 58190 MB. I run a full backup and I can't still shrink the log files.

    Help...what to do next?

  3. #3
    Join Date
    May 2003
    Location
    Pinoy in NJ
    Posts
    104
    OK I think I finally got this resolved.

    (1) I made a full backup of the transaction log
    (2) Only then I was able to shrink the transaction log to 200MB from 58GB to the minimum size (187 MB)

    What I'll be doing next.

    - Full back up of the database
    - Detach DB
    - Rename LOG file (still 58 GB)
    - Restore DB with no log to create a new LOG file.
    Last edited by ARPRINCE; 10-19-08 at 15:17.

  4. #4
    Join Date
    Dec 2007
    Location
    London, UK
    Posts
    741
    Why detach and restore if the problem is already fixed?

    Did you check if your log backups had failed at some point? One likely explanation for the log file growing so large is that your transaction log backups weren't happening for some reason.

  5. #5
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    better question. why are you in full recovery when you are apparently not managing regular transaction log backups.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  6. #6
    Join Date
    May 2003
    Location
    Pinoy in NJ
    Posts
    104
    Quote Originally Posted by dportas
    Why detach and restore if the problem is already fixed?
    I Detached and Attached. The size of the LOG file was still physically 58GB so I needed to re-initialize it and free disk space. By attaching the MDF file without the LDF file (which I renamed, zipped and deleted afterwards), it reintialized my log file.
    Last edited by ARPRINCE; 10-20-08 at 10:13.

  7. #7
    Join Date
    May 2003
    Location
    Pinoy in NJ
    Posts
    104
    Quote Originally Posted by Thrasymachus
    better question. why are you in full recovery when you are apparently not managing regular transaction log backups.
    Good point! I accepted the default configuration of the SQL server for the database. It was never a problem for 7 years and for some reason, it acted up when we changed the server's domain. I guess the permission to backup got screwed up when we did this last week since the old domain/owner was now non-existent.

  8. #8
    Join Date
    Dec 2007
    Location
    London, UK
    Posts
    741
    Quote Originally Posted by ARPRINCE
    By attaching the MDF file without the LDF file (which I renamed, zipped and deleted afterwards), it reintialized my log file.
    Unfortunately you also risk corrupting your data beyond repair by doing this. DBCC SHRINKFILE is the way to shrink the log file (after you have done a backup).

  9. #9
    Join Date
    May 2003
    Location
    Pinoy in NJ
    Posts
    104
    Quote Originally Posted by dportas
    Unfortunately you also risk corrupting your data beyond repair by doing this. DBCC SHRINKFILE is the way to shrink the log file (after you have done a backup).
    I believe the LOG file was corrupt already. I run a SHRINKFILE using the GUI initally but it was stuck to 58GB. After a full backup of the transaction log, SHRINKFILE was able to reduced the LOG SPACE USED from 99% to 0.003% but the weird thing is, the LOG SIZE was still physically 58GB.

  10. #10
    Join Date
    Dec 2007
    Location
    London, UK
    Posts
    741
    That is the expected behaviour. SHRINKFILE only removes UNUSED space from the log. If the log is full then it won't shrink until after you back it up. Nothing you've said indicates that the log was "corrupt".

  11. #11
    Join Date
    May 2003
    Location
    Pinoy in NJ
    Posts
    104
    Noted - thanks.

Posting Permissions

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