SQL Server Database Log Size

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?
csimmons1324IT ManagerAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Aneesh RetnakaranDatabase AdministratorCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Scott PletcherSenior DBACommented:
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 EngineerCommented:
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 DBACommented:
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 ManagerAuthor Commented:
Thank you for all the feedback!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.