I’m currently having to deal with a SQL Server transaction log that has got out of control. Disclaimer: I’m not a dba and this isn’t my area of expertise so please bear with me.
Currently I have a 115GB Transaction log file for a 500MB database which has (obviously) been poorly managed for some time for it to get in this state.
The top priority is to reclaim the space on the disk taken up by this file before we run out! I’ve been told increasing the size of the drive is not an option, even temporarily, and based on past growth, we need to act pretty soon.
As I understand it, the best approach is to keep the db in full recovery mode but take regular backups of the log file, monitor this over a period of time and adjust initial size and increment to suit. All okay.
Seeing as we take regular full db backups at midnight, would it be safe for me to temporarily put the database into Simple Recovery Mode (after one of these backups has run), shrink the log file to reclaim (virtually all of) the space and then put it back in to Full Recovery with the backup strategy mentioned above?
My thinking is that if something happened around this time, we could simply restore the full backup without using the logs.
A few extra details in reply to some of the answers and comments:
We do want to retain the ability to do a point-in-time restore so the database should remain in full recovery mode.
The reason that the t-log file has grown so large is that it has never been backed up. Verified as log_reuse_wait_desc returns ‘LOG_BACKUP’.