SQL 2014 - Transaction Log Files - Less than 05% Disk Space Being Approached

Does anyone have a recommended plan of action/strategy to immediately address an issue where there transaction logs are taking up 95% of drive space on the E: Drive?  It's for a SQL Server 2014 Standard configuration.
jduran04Asked:
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.

Aneesh RetnakaranDatabase AdministratorCommented:
seems like you are not taking regular transaction log backups (highly recommended on production )
if you don't worry about the data recovery since the last backup, change the recovery model to simple, shrink the transaction log file.
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:
First, you need to see what recovery model the dbs have, and see if anything is preventing log space from being reused.

SELECT name, recovery_model_desc, log_reuse_wait_desc
FROM sys.databases
ORDER BY name

If it's 'SIMPLE', then you've had some process use up a lot of log space.  You can shrink the log to gain space now, but it will likely grow again.

If it's not simple, i.e. it's 'FULL' or 'BULK-LOGGED', then you need to make sure that either:
(1) you do periodic log backups to allow log space to be reused OR
(2) you change the recovery model to SIMPLE, and then you can shrink the log to a reasonable size and there's a good chance it won't get as large as it was before.
1
Vitor MontalvãoMSSQL Senior EngineerCommented:
Apart from the above good comments it will also be good to move transactional log files to a different disk drive.
0
ZberteocCommented:
If this is a production environment then you have to leave the databases it in FULL recovery mode and schedule backups. Recommended FULL, Differential and Transaction Log backups.

Transaction log is the file that stores all the transactions that take place to the database, that is INSERT/UPDATES/DELETES ans also structural or settings changes.  If there are full/differential or transaction log backups then the space INSIDE the log file is released from the backed up transactions and can be reused, in which case the file will not grow anymore and will reach a stable size. If you don't do the backups then the space inside the logs cannot be released so the file HAS to grow until it will fill up the hard drive.

If you are not in the production environment then you can simply switch the recovery mode to SIMPLE, which will take care of the transaction logs.

If you leave the database in FULL recovery mode and do a backup then you can shrink the transnational log file.
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.