Results 1 to 6 of 6
  1. #1
    Join Date
    Apr 2007
    Location
    Big City
    Posts
    62

    Unanswered: How do I reclaim space in SQL Server and its logs file

    Hello,
    Please help
    how do i reclaim space in sql server ? We have log files - MDF and LDF which grew out of control
    How to shrink data or does the space get reclaimed once the data is deleted/truncat???


    Thank you,

  2. #2
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    DBCC SHRINKFILE. Look it up. Do not do it during busy production times or if your files are just going to grow that big again..
    “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.

  3. #3
    Join Date
    Apr 2007
    Location
    Big City
    Posts
    62
    may run this command below for all databases to manually truncate the log

    use [database name]
    BACKUP LOG [database name] WITH TRUNCATE_ONLY

    and then via enterprise mngr I will run the Shrink database command
    Also may I change the recovery model in the property of each db
    from Full to Simple, so that the log will not grow.????

    Thank you

  4. #4
    Join Date
    Aug 2006
    Location
    San Francisco, CA
    Posts
    136
    ypal,
    You are right, if you change the recovery model from full to simple your log will not grow as fast as in full mode, but remember what this means for your recoverability.

    Let’s say for example you run full nightly backups and hourly tlog backups, you could recover up to any point in time (after the full backup) until the last tlog backup was taken (less than an hour ago). Now if you switch the recovery model to simple, your tlog backups will be worthless, and you will only be able to recover from your full backup; losing all data from the current day.

    I recommend you shrink your tlog for now, and review what is causing your log to grow so fast.

  5. #5
    Join Date
    Jun 2004
    Location
    Long Island
    Posts
    696
    Quote Originally Posted by Reghardt
    ypal,
    You are right, if you change the recovery model from full to simple your log will not grow as fast as in full mode, but remember what this means for your recoverability.

    Let’s say for example you run full nightly backups and hourly tlog backups, you could recover up to any point in time (after the full backup) until the last tlog backup was taken (less than an hour ago). Now if you switch the recovery model to simple, your tlog backups will be worthless, and you will only be able to recover from your full backup; losing all data from the current day.

    I recommend you shrink your tlog for now, and review what is causing your log to grow so fast.

    Or you can stay in simple recovery mode, and do differential backups throughout the day.

  6. #6
    Join Date
    Apr 2007
    Location
    Big City
    Posts
    62
    Thank you for all suggestions, staying in simple mode

Posting Permissions

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