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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Apart from the above good comments it will also be good to move transactional log files to a different disk drive.
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.
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.
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.