SQL Server Database Log Size

csimmons1324 used Ask the Experts™
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?
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Database Consultant
Top Expert 2009
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
Scott PletcherSenior DBA
Most Valuable Expert 2018
Top Expert 2014
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.
Vitor MontalvãoMSSQL Senior Engineer
Distinguished Expert 2017
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.

Scott PletcherSenior DBA
Most Valuable Expert 2018
Top Expert 2014
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.
csimmons1324IT Manager


Thank you for all the feedback!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start Today