Large Transaction Log - Mostly Unused

Question

Large Transaction Log - Mostly Unused

mike 14496

Grasshopper

Points: 18
More actions
October 8, 2018 at 6:51 am

#377931

Hello Everyone,
I have a 2014 database that has a data file which is almost 7GB and a transaction log that is 9GB. It uses the full recovery model with full backups taken daily and transaction log backups taken every hour. The unused portion of the transaction log is 99%. The unallocated portion of the data file is 26%, the data portion is 52%, indexes are 16% and unused is 6%.
Do I have anything to be concerned about since the transaction log is so large but is mostly unused?
Thank you for your insights.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

NorthernSoul SSCertifiable Points: 6869 More actions · Answer 1

The log will have grown to 9gb for a reason, maybe a large transaction running or possibly it was in full recovery but the log backups were not being taken. If you are confident that it doesn't need to be 9gb and there are going to be no big maintenance jobs running then go ahead and shrink it down.

Thanks

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 2

mike 14496 - Monday, October 8, 2018 6:51 AM
Hello Everyone,
I have a 2014 database that has a data file which is almost 7GB and a transaction log that is 9GB. It uses the full recovery model with full backups taken daily and transaction log backups taken every hour. The unused portion of the transaction log is 99%. The unallocated portion of the data file is 26%, the data portion is 52%, indexes are 16% and unused is 6%.
Do I have anything to be concerned about since the transaction log is so large but is mostly unused?
Thank you for your insights.

Yes. The question would be, what made it so large compared to the MDF file? Even on relatively small databases like this one, it's normally a symptom of either bad code, some crazy one time event, bad growth settings, and/or not having backups. You've identified the latter of those has been taken care of.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)