Results 1 to 12 of 12
  1. #1
    Join Date
    Aug 2011
    Posts
    4

    Unanswered: LDF file is over 215GB

    Hi everyone,

    I'm fairly new to SQL server. Everything has been running smoothly until I've seen the hard disk space. I looked into it and found that character_log_1.ldf is nearly 220GBs. This is WAY larger than the database, and I'm starting to run out of space..

    I right clicked the database (named character) and went to properties, went to the Transaction Log and saw that there is a nice, tempting button that says Delete. It's not safe to delete the transaction log, right? What would happen? Would it affect the database?

    I need a way to get this thing under control fast, otherwise I'm going to run out of space and that is going to cause a ton of problems..

    Thanks in advance!

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    If you don't understand what the choices for the MS-SQL "Recovery mode" are, then you need to learn about them very, VERY soon. For right now, you almost certainly want them set to SIMPLE which trades off some recovery/reliability to get back disk space.

    How to: View or Change the Recovery Model of a Database (SQL Server Management Studio)

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

  3. #3
    Join Date
    Aug 2011
    Posts
    4
    Quote Originally Posted by Pat Phelan View Post
    If you don't understand what the choices for the MS-SQL "Recovery mode" are, then you need to learn about them very, VERY soon. For right now, you almost certainly want them set to SIMPLE which trades off some recovery/reliability to get back disk space.

    How to: View or Change the Recovery Model of a Database (SQL Server Management Studio)

    -PatP
    Okay, I set all of my databases to "simple". Will this decrease the size or just stop it from growing? If not, how can I decrease the whooping 200GB size.

    Thank you.

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Oops, my bad!

    Doing a log file shrink will tax your server hardware's IO bandwidth to the point that this may consume all of the IO bandwidth that your server has. If you ware working on an unmanaged SQL Server, you may want to declare "down time" just in case the server stops responding while the shrink occurs.

    Depending on your server settings, it is possible that the database log file may have already shrunk. This shrinkage would indicate that someone or something is attempting to do "brute force" management of your server, which is something that I would hunt down and disable as soon as possible. If the log files are still very large relative to the data files, then see:

    How to: Shrink a File (SQL Server Management Studio)

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

  5. #5
    Join Date
    Aug 2011
    Posts
    4
    Quote Originally Posted by Pat Phelan View Post
    Oops, my bad!

    Doing a log file shrink will tax your server hardware's IO bandwidth to the point that this may consume all of the IO bandwidth that your server has. If you ware working on an unmanaged SQL Server, you may want to declare "down time" just in case the server stops responding while the shrink occurs.

    Depending on your server settings, it is possible that the database log file may have already shrunk. This shrinkage would indicate that someone or something is attempting to do "brute force" management of your server, which is something that I would hunt down and disable as soon as possible. If the log files are still very large relative to the data files, then see:

    How to: Shrink a File (SQL Server Management Studio)

    -PatP
    Hi,

    Thank you very much for your reply again, but I have already solved the issue.

    Yesterday I did a lot of research as you suggested. I went to the database, right clicked it, and clicked Shrink Database. I changed the option to the log file, and clicked Shrink. The log file successfully shrunk from a whopping 220GB+ to a little over 2GBs. It's miraculous.


    Thanks again for all of your generous help, have a nice day.

  6. #6
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I'm always glad to help. Please note that I put the VERY in big red letters for a reason in my first response. You really need to understand what your choices are, and confirm that you can live with the consequences of choosing the SIMPLE option. SIMPLE is fine for many databases, but not for all databases by any means.

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

  7. #7
    Join Date
    Aug 2011
    Posts
    4
    Quote Originally Posted by Pat Phelan View Post
    I'm always glad to help. Please note that I put the VERY in big red letters for a reason in my first response. You really need to understand what your choices are, and confirm that you can live with the consequences of choosing the SIMPLE option. SIMPLE is fine for many databases, but not for all databases by any means.

    -PatP
    I have a quick question.

    I set my database to Simple.

    I was wondering, my database takes backups every 4 hours onto another drive just in case anything goes wrong or I need to roll it back (the database is running an online MMORPG game with thousands of players). Does the fact I have it set to simple mean I cannot restore the backup if something happens? A bit confused.

  8. #8
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    The recovery option does impact your RESTORE choices, but you can definitely restore a database backup that was made while the database was in the SIMPLE recovery mode.

    When you use the SIMPLE mode, the database backup is "complete" at the moment that the backup finishes. You can restore the backup to exactly that point in time using a single RESTORE command.

    When you use the FULL recovery mode, you have additional choices in terms of what you can restore and how you can do it. These choices are a more advanced topic that greatly extends the power of the backu/restore commands.

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

  9. #9
    Join Date
    Mar 2007
    Location
    Holmestrand, Norway
    Posts
    332
    I guess your MMORGP players will be pretty pissed if they have just completed a difficult task, you get some database issues, and have to restore a 4 hour (or even 24 hour) old backup, essentially meaning that your players loose a lot of in-game items and money. In my opinion, you should definitely look into the full recovery model, and have a look at differential and transaction-log bakcups.
    Ole Kristian Velstadbråten Bangås - Virinco - MSSQL.no - Facebook - Twitter

  10. #10
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    While I use the FULL recovery mode for almost all of my databases, I'm not sure if the recovery mode makes a lot of difference for what HanMan5000 is doing. At least in my experience, when a MMORPG database goes down the underlying problem is usually due to hackers or as a side effect of a DDoS. These are problems where a database restore will fix some of the damage, but won't really restore usability until the root cause is fixed.

    Most MMORPG players won't even remember exactly where they were after this kind of problem. Most of the gaming systems that I've seen don't have the same level of robust error recovery that commercial systems should have. Getting the database/game state back to the point where the failure occured would be nice, but by the time that the root problems are fixed two or more days later the players may not remember exactly where they were when the system crashed... Finding a stable state where all of the app and web servers can come back up in a reliable state may be far more important to everyone involved than precisely when that reliable state occured!

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

  11. #11
    Join Date
    Mar 2007
    Location
    Holmestrand, Norway
    Posts
    332
    They may not remember exactly where they were, but they will definitely notice, and complain, if their state of the art gear is missing.
    Ole Kristian Velstadbråten Bangås - Virinco - MSSQL.no - Facebook - Twitter

  12. #12
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by roac View Post
    They may not remember exactly where they were, but they will definitely notice, and complain, if their state of the art gear is missing.
    Whether or not they had the gear!

    I've had gamers complain about "loosing" gear and character attributes that I never created (I wrote the game). I've become more than a smidgeoun skeptical on that front.

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

Posting Permissions

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