Estimating suitable log file size (MSSQL Server)

Posted on 2012-08-15
Last Modified: 2012-08-31
Hi Experts.

I have an application that uses MS SQL Server. The application does not uses transactions explicitly but as I understand, applications create or use default transactions. The fact is, that the log file is growing and I need to reduce it. The client is asking me to recommend a minumum log file size suitable for the application but I have no idea how to calculate such size, based on how my program works. So my question would be:

How do you calculate the espace needed for an application, regarding the log size (MS SQL Server) based on the instructions you send to the database (inserts, updates, etc)?

Note: as I don´t use explicit transactions that log information is useless for my application.

Thank you in advance.
Question by:arturosm
    LVL 26

    Accepted Solution

    Transaction log is used to log all the statements that in any way will modify the status of the database. It is how SQL server works and any insert, update or delete, which are practically transactions, are first logged and then committed to the database if there are no errors. The transaction log could theoretically grow to any size it all depends what it is done.

    If you have a database of lets say 10GB but you do an import from a file of 100GB the transaction log could practically go up to 100GB and in the end when committed the database will grow to 110GB and the transaction log will still be 100GB unless is shrunk.

    So the estimate should start with the total size of the database itself, considering its future organic growth and then at least double that to make sure the log will have enough space when needed to grow. All depends as I said of the way database is used. If there are no huge import foreseen or some big temporary files as a result of intensive queries then the extra space can be limited but to start with double the size of the whole database is a common sense.

    In regards to the transaction log size, even if it gets to 100GB as in the example I gave that doesn't mean that it will still necessarily grow in the future because as soon as the transactions are committed the space INSIDE the transaction log is reused in a circular mode. So it is possible that will never grow bigger than that.

    If the size of 100GB for the log file is to much then it can only be shrunk to a minimum size that is needed for the transactions still in process. You can never shrink the log to 0. If the database is in simple recovery mode, which in production is not recommendable, then it will automatically shrink after transactions are committed. In full recovery mode there should be a periodically full backup scheduled, at least once a week, followed by differential backups more often, maybe daily, and transaction log backup done  hourly or even more often. If the transaction log gets too big can periodically shrunk with:

    DBCC SHRINKFILE(log_file_name, 2)

    The idea is that the log file grows only when it needs more space than is already allocated to it, basically when the free part of the log is smaller then the current transactions that are logged.
    LVL 1

    Assisted Solution


    are you using some kind of data replication technology like log shipping or replication if not and you are not using log backups as well you can change your database to simple recovery model, and your log file will not grow any more.

    Author Comment


    thanks for your responses. I know the client backup the database daily and the application does not uses the log data, never. For me, this log issue is just a secondary effect of using MS SQL Server. What would be the worst scenario of using simple recovery model?

    LVL 26

    Expert Comment

    Your database breaks one second before the daily backup. You lost all the transactions for the last 24 hr. That is pretty much unacceptable for almost all the cases.

    A simple recovery is not recommendable to a production environment unless you have a case where you update the database periodically through some import or syncing process but even then is risky.  

    There is no justification not to use full recovery as it is not at all a very complicated process. It can be set up through the Maintenance plan wizard in minutes.

    Author Closing Comment

    Thank you.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
    Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
    Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
    With the advent of Windows 10, Microsoft is pushing a Get Windows 10 icon into the notification area (system tray) of qualifying computers. There are many reasons for wanting to remove this icon. This two-part Experts Exchange video Micro Tutorial s…

    759 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

    10 Experts available now in Live!

    Get 1:1 Help Now