Results 1 to 9 of 9

Thread: Shrink Log File

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

    Shrink Log File

    My DB's recover model is SIMPLE. Is it OK to schedule a SHRINK FILE only on the log files regularly? Any GOOD vs BAD about my plan? I want to do this because the log files keeps on increasing.

    Right now, the log file s on ENABLE AUTOGROWTH, FILE GROWTH = 10%, RESTICTED FILE GROWTH = 2,097,152.

    TIA

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,539
    The log file keeps growing, because there is some data load, update or purge that requires that much space as a single transaction. If no one is going to do anything about the large load/update/whatever, you may as well accept that the log file will grow to that size, and allow it to stabilize.

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,569
    As MCrowly pointed out, your log file will likely quickly grow to the same size again, if there is a process running on your database that requires it.
    Expanding the log file eats up resources and will make your queries run more slowly, so you don't want to shrink it on any regular basis.
    If it's not practically useful, then it's practically useless.

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

  4. #4
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,634
    There is nothing good, just bad. DML operations are queued/suspended when the growth occurs, and with percentage-based setting each consequtive growth will be longer than the previous one.
    "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
    May 2003
    Location
    Pinoy in NJ
    Posts
    103
    Quote Originally Posted by MCrowley View Post
    The log file keeps growing, because there is some data load, update or purge that requires that much space as a single transaction. If no one is going to do anything about the large load/update/whatever, you may as well accept that the log file will grow to that size, and allow it to stabilize.
    We use the server mainly as a staging DB for our BI. I have SSIS packages that runs weekly that imports data from our outside vendors and our AS400. I prep the data and transfer it to another SQL server. The main reason I want to shrink the log id because it is 35% the size of my data file which is approx 50GB.

  6. #6
    Join Date
    Aug 2008
    Posts
    140
    As earlier mentioned if it's a regular thing - from a performance perspective it's better to leave the expanded size.
    Do you need the space for any other transactions on other databases that may have the logs on the same drive?
    ---------------------------------
    www.sqlserver-dba.com
    www.dba-db2.com

  7. #7
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    14,948
    Instead of scheduling a shrink, just drop the database and recreate it. That way you don't hit any of the problems, and can efficiently set the log file to whatever size you want.

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

  8. #8
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,569
    Yoikes!
    Drop and recreate the database Pat?
    Have to take issue with you on this.
    Assuming this is a production database...
    If it's not practically useful, then it's practically useless.

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

  9. #9
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    14,948
    If the database is simply a scratch space for BI where data is loaded, manipulated, then copied to another server then there is nothing of long term value except for the schema. It would be simpler and more efficient to drop the database and rebuild the schema than it is to truncate the tables (or heaven forbid to delete the rows) then shrink the log on a regular basis.

    If there was some reason to keep the database around, then I question whether a scheduled truncation of the log makes any sense. Shrinking the log is a really poor choice with little or nothing that could justify doing it more often than annually or quarterly in my opinion. If you do something between one and four times per year that causes the log file to bloom in size by a factor of five or more, then there might be a good reason that would justify shrinking the log.

    I'm pretty sure that you and I are on the same page. If there is data in the database that needs to be kept over time and the data is worth working to ensure that queries perform well, then shrink is probably a poor choice. If the data is volatile meaning you can refresh it at will without losing anything of value, then there is no need to keep the database and a drop/rebuild of the schema is a good fit. If performance is of no concern, then a shrink won't hurt anything (but I still wouldn't do a shrink).

    -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
  •