Post reply

Shrinking Log Files

  • September 9, 2003 at 12:52 pm

    #64388

    I know that most times we have the opposite problem, but I want to know why my transaction log files keep shrinking! FYI - this is a SQL 7.0 SP3 server running on Windows NT 4.0 SP6a. I need to install a vendor supplied update script that is pretty read/write intensive. To prepare for this, I have increased my log file size from 40mb to at least 250mb but the setting doesn't stick. When I check back later on or the next day, it's back down to around 40mb. I do not have the Autoshrink option turned on, and I don't think that this setting would apply to the logs on SQL 7.0 anyway. The Autogrow option is turned on for both the DB and log files. Data file size now is right around 6 gigs total with about 2.5 gigs of free space. We run hourly transation log dumps 24/7.

    I would like to know why this keeps happening, and would also like to know if there's a way to enforce a minimum log file size. My concern is that I'll start the update, then have to wait while the log file allocates more disk space to complete the scripted tasks. I only have a limited amount of down time to get these updates done. I suppose I could just set truncate log on checkpoint after getting a full back up (which I usually do anyway) but I'd really like to know why the log file size keeps shrinking and would also like to know if there's any way to prevent it. Any assistance or direction is appreciated. Thanks!

    Cheers,

    Sandman

    My hovercraft is full of eels.

  • September 9, 2003 at 1:34 pm

    #473303

    Is there a job scheduled to shrink the log file? I seen one of my application does that. It comes from application installation.

  • September 9, 2003 at 2:19 pm

    #473307

    There's nothing in EM and I've only set up the routine type jobs. I queried msdb.sysjobs just to be sure, but it only lists the the maint. plan jobs and a couple of custom re-index and clean up jobs that I set up myself.

    I guess it depends on the amount of activity that the DB gets, but to my mind 50 mb is too small for a DB of this size even with the hourly log dumps. I know in SQL 6.5 we used to size the logs at 25% of total DB space as a rule of thumb and this is no where near that. Granted, the DB has been live for 4 years now, so a lot of this data is probably ready for archiving, but I still think the log size is small. Thanks Allen. If I find anything else, I'll update the post but I'm still looking.

    "Remember to take credit for the good things you say and do." Anonymous

    My hovercraft is full of eels.

  • September 9, 2003 at 2:50 pm

    #473315

    I set it to 150 mb again and will see what happens in the morning. It's been 45 minutes since the last log dump and only 8 mb of the log is in use so far. And this is during what should be a fairly heavy activity time. Maybe SQL Server really does know better than I do

    FYI - The only options set on this DB are Auto update and create statistics. Still looking...

    "Remember to take credit for the good things you say and do." Anonymous

    My hovercraft is full of eels.

  • September 9, 2003 at 3:36 pm

    #473322

    You can possibly also run a profiler trace looking for "Log File Auto Shrink" events.

    Cheers,

    - Mark


    Cheers,
    - Mark

  • September 10, 2003 at 6:42 am

    #473401

    Thanks Mark,

    Good idea, but that's not an option in 7.0 unfortunately. When I try it from a 2000 client, I get an error saying it can't execute due to the low server version (I can't wait to upgrade this server to SQL 2000 but it's not in the budget this year).

    Darn thing shrank back to less than 40 mb overnight again! Interestingly, I have a test DB on the same server which is an older copy of this DB, and that log size has remained the same for the last few days. Test DB doesn't get near the activity of Prod and the log dumps are only once every 12 hours but other than that, they should be pretty much the same. I'm sort of stumped at this point. Thanks for the suggestions, though. I'll keep looking.

    My hovercraft is full of eels.

  • September 10, 2003 at 7:13 am

    #473407

    I've seen this behavior before. I'm not sure, but it appears to be caused by the Tlog backups. Our Logfile sizes quite often return to the size that they were originally created at. The fix for this problem is to create an additional file for the log, and make sure you create it at the size that you want. If you feel strongly about having only 1 log file, you can delete the other with TSQL.

  • September 10, 2003 at 8:40 am

    #473441

    Thanks Kevin. From what I've seen, I'd have to agree that it seems to occur concurrent with the log dumps. I guess if I get really annoyed with it, I can just detach the DB, re-attach single file and then size the newly created log to where I'd like it to be. Although from what you're saying (thinking out loud here), this will create a new 1mb log file by default and it might try to shrink back to that when it can which gets me nowhere.

    The second file idea is a much less drastic method and certainly worth a try. I've got lots of room on the mirrored drive reserved for the logs. The way it is, the small size doesn't appear to be hurting performance any (my users generally scream whenever the server burps) but I am still concerned about running my update scripts.

    I appreciate the help.

    My hovercraft is full of eels.

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply