troubleshooting Question

SQL Database Transaction Log file keeps growing

Avatar of BlueGoose
BlueGooseFlag for United States of America asked on
DatabasesMicrosoft SQL ServerVirtualizationSQL
7 Comments2 Solutions127 ViewsLast Modified:
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
ASKER CERTIFIED SOLUTION
Zberteoc

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 2 Answers and 7 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 2 Answers and 7 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros