Avatar of BlueGoose
BlueGooseFlag for United States of America

asked on 

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
DatabasesMicrosoft SQL ServerVirtualizationSQL

Avatar of undefined
Last Comment
BlueGoose
Avatar of Michael Pfister
Michael Pfister
Flag of Germany image

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
Avatar of BlueGoose
BlueGoose
Flag of United States of America image

ASKER

In my database maintenance plan we do nightly backups of the transaction logs.   Is what you mentioned something different?
ASKER CERTIFIED SOLUTION
Avatar of Zberteoc
Zberteoc
Flag of Canada image

Blurred text
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
SOLUTION
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
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?
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.
Avatar of BlueGoose
BlueGoose
Flag of United States of America image

ASKER

That worked perfect guys!  Thank you!
Microsoft SQL Server
Microsoft SQL Server

Microsoft SQL Server is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.SQL Server is available in multiple versions, typically identified by release year, and versions are subdivided into editions to distinguish between product functionality. Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning.

171K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
Ask the experts
Read over 600 more reviews

TRUSTED BY

IBM logoIntel logoMicrosoft logoUbisoft logoSAP logo
Qualcomm logoCitrix Systems logoWorkday logoErnst & Young logo
High performer badgeUsers love us badge
LinkedIn logoFacebook logoX logoInstagram logoTikTok logoYouTube logo