SQL Database Transaction Log file keeps growing

I'll start out that I'm a novice and by no means a DBA.  Hopefully I explain this correctly and provide information.

We use a product called KANA and just upgraded to a newer release on a new VM.   SQL Server 2014

Without trying to say everything in one post (I can answer any questions if more info need) I'll start by stating my main issue.

The log file in the data folder is called Kana_Log.  Autogrowth is set to 10 percent and maxsize is unlimited.  

We do nightly transaction log backups and backup the database once a week.

I'm pretty sure we have have everything set up just like the older system.

What is happening is the kana_log file never reduces in size.  It just keeps getting larger and larger.  Recovery model is set to full.  I have reduced the size of the log file by using the following commands

ALTER DATABASE [mydatabase] SET RECOVERY SIMPLE
DBCC SHRINKFILE(<log_file_name_Log>)
ALTER DATABASE [mydatabase] SET RECOVERY FULL

The way things worked on the old server was I believe that after a full back up the log file would reduce in size.  It does not happen on the new server

I know I probably left a bunch of info out...please ask for more info in order to come to a solution

So bottom line is that the kana_log file just keeps growing and growing and never reduce in size unless I run the above commands.  

I may be wrong but isn't the log file supposed to reduce in size after a backup?

Thanks and look forward to any ideas on solving this
BlueGooseAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
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.

Michael PfisterCommented:
You need to run a BACKUP LOG. A FULL backup won't shrink the transaction log.
https://docs.microsoft.com/en-us/sql/relational-databases/backup-restore/back-up-a-transaction-log-sql-server

HTH
BlueGooseAuthor Commented:
In my database maintenance plan we do nightly backups of the transaction logs.   Is what you mentioned something different?
ZberteocCommented:
The fact that you backup the log file doesn't affect the size. Transaction log is NEVER supposed to shrink unless you deliberately do that! However, what the log backup does is to free up the space INSIDE the log file in order to be reused. Eventually it will stabilize to a size where it will not grow anymore.

Having said that the way your maintenance plan is setup is faulty! If you make a weekly full database backup then I recommend you to make a daily differential backup as for the transaction log backup you should bring it at at least once an hour! This will keep even more in control its size. So modify your maintenance plan like this:

1. FULL database backup once a week, preferably on one of the week-end nights when the usage is at minimum, i.e. Sunday morning at 1AM.
2. DIFF backup once a day, sometime after hours or peak usage, i.e. 10PM or , 12AM
3. LOG backup daily at least once an our, preferably every 15 minutes if you have a medium to high transaction activity. The more often you do the log backup the less space will log file need to grow but most importantly this will ensure you a better point in time recovery in case of failure!

After you do that you can shrink the transaction log using the command:

bcc shrinkfile(<database_logical_log_filename>,0)

But only once and never after unless some extraordinary circumstances demand it.

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
Active Protection takes the fight to cryptojacking

While there were several headline-grabbing ransomware attacks during in 2017, another big threat started appearing at the same time that didn’t get the same coverage – illicit cryptomining.

Michael PfisterCommented:
Add a plan to backup only the log files multiple times a day.  The log won't shrink but SQL should reuse the free space. https://community.spiceworks.com/topic/465564-db-logs-not-shrinking-after-backup-on-sql-server
Vitor MontalvãoMSSQL Senior EngineerCommented:
A backup plan needs to fit your needs and it looks like the one you're having is not.
A weekly full backup doesn't seem to be enough, especially because you're changing the Recovery Model often which means that you can throw to trash all transaction log backups you made and with that the following question raises:
- Do you really need a full recovery model in that database?
Anthony PerkinsCommented:
Exactly. Unless you are using Avalability Groups or something that requires Full Recovery Model, save yourself the trouble and leave the Recovery Model as Simple and stop the Transaction Log backup.  As it stands, you cannot use the Transaction Log backups even if you wanted/needed to.

When you have had time to do the necessary research and fully understand if/why you need a Full Recovery Model, then setup a full backup plan which includes restoring and testing the restores.
BlueGooseAuthor Commented:
That worked perfect guys!  Thank you!
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
Virtualization

From novice to tech pro — start learning today.