Best Practices - MSSQL Log File - Settings for Shrinking

Posted on 2005-05-05
Last Modified: 2011-08-18

I have run into a situation which on a regular basis the transaction log for my ms sql database is becoming full and causing our application not to work properly.

This happened two weeks ago after about two months of running a new application called FBO Manager.  It is for running the point of sale at a small airport.  Anyway at that time I set the recovery method to simple and shrunk the log file down and that was fine.  Then I set "automatically shrink" and reset the recovery method.

After two weeks the log file had filled up again.  I am not on site there all the time so It was not being monitored.

Anyway I would greatly appreciate it if someone more knowledgable could let me know the best way to set the settings relating to database size so it does not require a manual shrinking every two weeks.

I work with several other applications that run on mssql and I have never run into this before, so I apologize in advance if this is a ridiculouslly easy question or I haven't phrased something correctly.

Thanks in advance.

Question by:skykuhl
    LVL 75

    Expert Comment

    by:Anthony Perkins
    When was the last time you did a backup on this database?

    Author Comment

    I have the maintenance set to backup every night and it seems to be working.
    LVL 75

    Accepted Solution

    >>I have the maintenance set to backup every night and it seems to be working.<<
    In case you are wondering the lack of backups is usually a prime subject in these cases.  Failing that I am not sure.  Hopefully some DBA will step up to the plate and help you out.

    Expert Comment

    First thing to be aware of is if you are using simple recovery and your DB goes south you will only be able to restore to the point of the last backup which in your case sounds like the previous evening. If happned to lose the DB late afternoon all that days activity would be lost.

    For a production DB, you might want to consider changing to the full recovery model and doing transaction log backups on an hourly interval. Something like every hour, every four etc. depending on how much data you are willing to lose. If you need to restore and the disk your backups is on (which should be different that the disk your DB is on) is available then you can restore the backup and all the transaction logs up until the point of failure.

    If you run the DB maintenance wizzard it will walk you through all the necessary steps. I believe it defaults to weekly tlog backups so you'll need to modify the schedule if you go with something like the approach I've described.


    Author Comment

    Ok, I understand.  I will go over my backup settings and look at how it is working.

    However what about the settings related to the growth of the transaction log file?  How should I have those set?  Should I be limiting growth or allowing unlimited growth?  

    If I understand correctly I should be able to have it using the default settings, which I think allow unlimited growth and then reduce the file by backing up.  But in that case I don't know why it has stopped working twice in the past two weeks due to the log file size.  So how do you recommend that I set those settings?


    Assisted Solution

    It's a good idea to set a max file size and for it to be less than the max amount of space you can afford on the disk. Ideally, the log will be on it's own disk (array, RAID1 if possible) so you'd set it to be a bit smaller than the disk so you have room to work if it gets full (you can expand the size while you address the space issue).  Just be aware that if you have multiple DBs with log files in the same directory you'll need to configure them in such a way that one log getting full won't take all the disk space from the others.

    With regard to shrinking it -if you have the space you probably want to leave the size alone. Shrinking will require the OS to re-allocate space for the file the next time it needs to grow. Having the space pre-allocated is supposed to be faster. Realistically, the difference  is probably negligible.



    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Join & Write a Comment

    Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
    The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
    Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
    Via a live example, show how to setup several different housekeeping processes for a SQL Server.

    755 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    19 Experts available now in Live!

    Get 1:1 Help Now