Sql server 2012 tranction log keeps growing

I am having an issue with the size on the transaction log on one of my databases. The database is around 90 gigs and the transaction log is about 110 gigs.  I have a maintenance plan that runs nightly full backup 7 days a week and hourly transaction log backups 24/7.  About two months ago  I tried the shrink the log file.  I first did a full backup and then a transaction log backup.  I then shrank the log file down to 30 gigs.  After a few minutes I started seeing the log file growing again.  It went back up to 80 gigs.  I am not a dba, but it was my understanding  that if I am doing regular transaction log backups, it should keep the size of the log file small.  Does anyone know why the log file should be growing?

Thanks,

Bill
bjenningsAsked:
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.

Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
Are you on FULL recovery model? If you are, then it should truncate the transaction log at each Full backup.
0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
note: "truncate log" does not mean the file will physically shrink", but just that the space internally is marked to be reused
and I also ask: which recovery model are you on: full or bulk-logged ?
in those 2 cases, the regular t-log backup should solve the issue, BUT if the file continues to grow, you likely have some old open transaction which keeps the file from not growing:

check out the output of  this command:
DBCC OPENTRAN

usually, it's some replication transaction or the like which is the culprit
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
If you set tlog backups it's because your database model it's either FULL or BULK-LOGGED. Can you check which one it is?
Like Guy wrote you should have some transaction running since after shrink the tlog didn't come down from 30GB. Check for some long running jobs in SQL Server Agent.
And which kind of database are we talking about? I mean, how's the data are updated? By users with an application? By some import method (SSIS, Bulk-Insert, Replication, ...)?
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

bjenningsAuthor Commented:
Hello Guys...Thanks for the quick replies!

The database is set full recovery.  I ran the DBCC OPENTRAN and I did not find any old open transactions.   So tonight I am going to do a full backup of the database and then do a transaction log backup.  Once this is done I will then shrink the log file down to 30 Gigs.  

I heard that shrinking the log file could fragment the database....Should I worry about shrinking the log file?

Thanks,

Bill
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
Shrinking data files can fragment the database. Log files not but can affect the performance since you are shrinking a file that will grow later, and during the growth process can affect the database performance.
And with all the shrinks and grows you should check the number of VLF's. You should be careful with high number of VLF's (thousands aren't good).
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
bjenningsAuthor Commented:
Hello Vitor...Thanks for the reply.

I ran DBCC LOGINFO on my production database.....I have over 4 thousand vlf files.  When you say to be careful with a high number of VLF's...How can I reduce the amount of vlf's?

Thanks,

Bill
0
Scott PletcherSenior DBACommented:
To clear VLFs, you would typically shrink the log down to its minimum size possible, then re-grow it in relatively large chunks to the size you want it to be.  Log space must be pre-formatted so how much you can allocate at one time will depend on your drive speed; the db will be paused while the new space is being formatted.  You also want to make sure the autogrow is a reasonable, fixed amount (not a percentage).

USE [<your_db_name>]
--first use sp_helpfile to get the logical log file name (it will be the first column in sp_helpfile output)
DBCC SHRINKFILE ( [<logical_log_file_name>], 1 )
ALTER DATABASE [<your_db_name>] MODIFY
    FILE ( NAME = [<logical_log_file_name>], SIZE = 6GB,  FILEGROWTH = 50MB ) --or 100MB or 200MB, if you think it necessary and your disk subsystem can handle it
ALTER DATABASE [<your_db_name>] MODIFY
    FILE ( NAME = [<logical_log_file_name>], SIZE = 12GB )
ALTER DATABASE [<your_db_name>] MODIFY
    FILE ( NAME = [<logical_log_file_name>], SIZE = 18GB )
ALTER DATABASE [<your_db_name>] MODIFY
    FILE ( NAME = [<logical_log_file_name>], SIZE = 24GB )
ALTER DATABASE [<your_db_name>] MODIFY
    FILE ( NAME = [<logical_log_file_name>], SIZE = 30GB )


Btw, you do not need to take a full db backup in order to work with the log file.  At most I would just do a differential backup.  For log work, Just take a log backup and shrink the log.
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
ScottPletcher gave a good explanation how to do it but how to know the best chunck size?
Kimberley Tripp has a very good article about it. You should give a read on it so you can understand better the VLF's.
0
bjenningsAuthor Commented:
I just tried shrinking my transaction log down to 30 gigs, but only went down to 80 gigs.  It does say that the log file has 99% unused space.  Does anyone know why that would happen?

Thanks,

Bill
0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
yes, if the last used VLF are on the end of the file, you get that.
now, just wait a couple of minutes, redo a tlog backup, retry the shrink log file operation.
at some point, it will work
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
Or if you transaction log was set 30GB as initial size it won't shrink less than that value.
Can you check the available space in tlog file?
And also if the database is part of a replication?
0
Scott PletcherSenior DBACommented:
>> Or if you transaction log was set 30GB as initial size it won't shrink less than that value. <<

That's not true.  You can explicitly shrink a file to a size smaller than what it was initially allocated as.


As to the file not shrinking, it is as Guy said: the currently active VLF is near the end of the file.

DBCC LOGINFO shows which VLF is currently active; it's the one with "status = 2".  Once that status moves back to (near) the beginning of the log file, then you will be able to shrink the log.
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
That's not true.  You can explicitly shrink a file to a size smaller than what it was initially allocated as.
Right. Thanks for the correction.
We can shrink the transaction log to the minimum of 1 VLF, so the smallest it can be is the value of 1 VLF. Only if you has a VLF of 30GB than you can't shrink less than that size.
0
bjenningsAuthor Commented:
Thanks everyone for the replies!!  I am going to shrink the log again tonight in a maintenance window.

Thanks,

Bill
0
bjenningsAuthor Commented:
I was able to shrink the log and it has not grown in over a week!  Thank you all for your help!!

Thanks,

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