SQL Database Transaction Log file keeps growing

BlueGoose
BlueGoose used Ask the Experts™
on
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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Author

Commented:
In my database maintenance plan we do nightly backups of the transaction logs.   Is what you mentioned something different?
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.
Should you be charging more for IT Services?

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

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 Engineer
Distinguished Expert 2017

Commented:
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?
Top Expert 2012

Commented:
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.

Author

Commented:
That worked perfect guys!  Thank you!

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 7-Day Free Trial