SQL Server Database Log Size

Posted on 2014-08-18
Last Modified: 2014-08-27
I am setting up SQL Server 2014 on a new server.  I will be moving our existing ERP database to this new server.  Our current ERP database is 4 GB.

With SQL 2014, I am creating a new database to restore the backup to.  I am setting the inital size of the database to 6 GB but I am not sure what I should set the initial size of the log file to.  I have enabled autogrowth on both the DB and log file.  The DB will grow by 1 GB and the log will grow by 256 MB.  

I do not know much about SQL Server.  Do these settings look okay?  What is your recommendation for an initial log file size?
Question by:csimmons1324
    LVL 75

    Accepted Solution

    I would go with a rate of 1 GB for the logs depending on the traffic will add more. Also I would configure Instant Initialization for the sql server service account (You can grant the permission Performance Volume Maintenance Task through secpol.msc to the service account of SQL Server. )  if you have this enabled, then you can reduce the size of your mdf  file and its growth (although its not necessary)

    You also have to make sure that you take a regular transaction log backup
    LVL 68

    Assisted Solution

    Those are very large growth values; I'd probably cut each in half, to 512MB & 128MB.

    But, as long as you've enabled IFI, the data growth will be almost instant anyway.  However, the entire log space must always be pre-formatted on disk, which is entirely dependent on drives speed.  Log growth stops other db activity, so be sure to test to make sure growth of 256MB (or 128MB) does not take too long.

    You also need to decide ASAP on the recovery model: SIMPLE or FULL.  This is a critical choice that affects how you can recover/restore and the steps you must take to allow for and prepare for a recovery/restoration.
    LVL 44

    Assisted Solution

    by:Vitor Montalvão
    If you are restoring a database you don't need to create an empty one first. Everything that you'll do in the new database will lost as soon as you restore the backup. So the initial size will be always the size that it's in the backup.

    Be careful with setting the auto-growth to the transaction log file because of virtual log files (VLF). Excessive VLFs can occur when a transaction log is resized many times leading to performance problems with the transaction log, including restore and recovery times.

    The ideal value for this metric should be no more than 50. For calculating the expected number of VLFs here's the breakdown for chunksize:

    chunks less than 64MB and up to 64MB = 4 VLFs
    chunks larger than 64MB and up to 1GB = 8 VLFs
    chunks larger than 1GB = 16 VLFs

    Meaning that if you create a 1GB transaction log file you'll have 16 VLFs
    A 256MB will have 8 VLFs and if it grows 4 times (256MB each time) you'll have 32 VLFs when the file reaches 1GB, so the double of a 1GB tlog file if created at once.

    I suggest you a 1GB transaction log file and disable the auto grow. You can monitor the file and when reaches 70%-80% from being full you can schedule a manual grow for out of busy hours so it won't impact the performance.

    LVL 68

    Assisted Solution

    You also don't want VLFs that are too large.

    I would never disable autogrow.  If you do, be aware that most (all?) db processes will STOP if the log needs to expand and can't.

    Author Closing Comment

    Thank you for all the feedback!

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    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 …
    In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
    This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
    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.

    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

    9 Experts available now in Live!

    Get 1:1 Help Now