SQL 2014 Database Log file size

Database is 140GB  Log file is 133GB
Set to full recovery mode
Here is the Maintenances  plan I have setup for this database:
1. Full backup that occurs nightly at 1AM (Exec BackupDB)
2. Hourly Log backups (Exec BackupLog) Does not occur during full backup.
3.  Weekly Database Integrity checks On Sunday at 12am
4. Depending on percentage will do a Rebuild or Reindex Sunday  at 4AM

The log file has grown 20 gbs in the last 2 weeks.

My questions:
1. Am I doing something wrong?
2. How can I stop this from growing so much?
3. I read that shirking the log file is bad, is that true?

Any suggestions or pointers are appreciate.

The Goal would  follow best practices and keep the log files as low as possible without losing performance.

I am still fairly new in the SQL world.
jyoung127Asked:
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.

PadawanDBAOperational DBACommented:
When you say the size of the database is 140GB, are you talking about the data files or the size of the database (which, to me, would include data files and transaction log files)?  First things first, I would check the history on that maintenance plan and make sure that the transaction log backups are completing successfully.

Edit:  I am also notoriously bad at reading an entire question before posting a comment, so I missed your questions:

1.  The setup sounds fine
2.  If your transaction log is too large, the transaction log is actually being used, and you are successfully taking transaction log backups every hour - you need to increase the frequency of the transaction log backups.
3.  Repetitive shrinking of your transaction log file is indeed bad.  It causes fragmentation of the file as well as leading to tons of VLFs, which hurts transaction log performance at extreme numbers.

For completeness, to check the percentage of your transaction logs that are being used, you can use:

dbcc sqlperf( logspace );

Open in new window

0
jyoung127Author Commented:
The Size of the files are as followed:
DataBaseName_live  = 241 MB
DatabaseName_1_data = 161GB (Main data file) ***bigger than I original  said***
DatabaseName_log = 136Gb( Log files)


Please excuse me if I am not getting you the correct information.
All the jobs show they have successfully completed. This plan has been in place for a while now.
0
jyoung127Author Commented:
Log size is 136867 MB
Log size used % is .8450266
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

PadawanDBAOperational DBACommented:
You're fine!  You got everything I needed.  The quickest way to do this would probably be to leverage perfmon and setup a collecter set to track the SQLServer:Databases - Percent Log Used \ <All Instances> counter for a few days so that you can track the utilization of the transaction logs over time.  That will give you an idea of when/if you are generating enough activity to completely fill your transaction logs within a one hour period.  I do question the legitimacy of generating that much log activity with such a small amount of data, though.

A good walk-through on setting up collector sets if you haven't done this before: http://heineborn.com/tech/perfmon-how-to-record-server-performance/
0
jyoung127Author Commented:
Thank you I will set the collector up.

For now I should not increase the frequency of the transaction log backups? Wait for the results of the collector?
0
PadawanDBAOperational DBACommented:
You're certainly not going to hurt anything by increasing the frequency.  I've seen perfectly valid arguments for doing transaction log backups every minute (http://www.brentozar.com/archive/2014/02/back-transaction-logs-every-minute-yes-really/).  All I can say is that if you increase the frequency of your transaction log backups to a minute, you best be automating your restore procedure, cuz ain't nobody got time to create restore database commands for upwards of 1440 transaction log backups (and don't touch that dial until you have it automated).  The reason I am recommending the perfmon counter is I'm slightly suspicious of something more insidious going on.  To generate that much transaction log activity in one hour  with a data set of 160GB is curious, to say the least.
0

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:
I strongly suspect something is preventing the from being truncated after it is backed up.  It's certainly possible you wrote roughly 80% of 133GB to the log in an hour for one db, but it's not likely.

SELECT name, log_reuse_wait_desc, *
FROM sys.databases
WHERE name = '<db_name_in_question>'

If the wait desc is anything but 'Nothing', then  you have further investigation and work to do.
0
jyoung127Author Commented:
It does show nothing.

Is there any way of lower the size without Shrinking log file?
0
Scott PletcherSenior DBACommented:
Wait, maybe I misunderstood.

Log size is 136867 MB
Log size used % is .8450266

I bet that's 0.84%, not 84%, which I thought it was.

You should be able to shrink the log file now.

USE your_db_name
DBCC SHRINKFILE ( 2, 32768 )
0
jyoung127Author Commented:
Thank you both for the help. I increased my log backs up and also got the log file down to size that is manageable.
You guys are great!!

PadawanDBA  
I am still running the collector and if I see any thing out of the ordinary I will open a new thread.
0
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.