Preface this with the following: I am not a DBA. I see this is a fairly common issue, but everything points back to the transaction log backups. I've been tasked with something well outside my wheelhouse.
I set up our SQL DB (Roughly 20gb in size,) in FULL recovery mode with a nightly FULL backup that occurs every weekday at 4:30AM. This task checks integrity, reorganizes index, updates statistics, history cleanup, backup the full db, shrinks the db, and then maintenance clean up.
I then set another subplan for Transaction Logs. This backs up the Transaction Log every 3 hours during business hours (8am, 11am, 2pm, 5pm), also during weekdays. There are not that many transactions that occur really; in the years I've been here the actual DB has increased less than a gb.
The .LDF file for the server exploded over night on Saturday and with no monitoring or warning, the server is now at capacity. This had been running fine from the looks of the data usage report (and random eyeballing) for months. It looks like shortly after the backup started, at 4:36AM, the transaction log file kept growing to the point where it is sitting at 90gb, with a whopping 5MB free on the drive.
My initial thought was to move one of the older db backups off the server to alleviate space constraints; This worked for a few minutes before the .LDF file gobbled that up also.
Is the only real option I had to switch the DB to SIMPLE, shrink the transaction log, and then swap to FULL, take a backup, and resume where I left off? I attempted to do a compressed transaction log backup to the other drive on the machine and it still would not allow me to do a DBCC SHRINKFILE. It errored out saying the transaction log for the DB is full due to 'log_backup'. I attempted three transaction log backups and trying the DBCC SHRINKFILE each time.
In my testing environment the FULL->SIMPLE->FULL method seemed to work, but I am unsure why, or how this happened to begin with. Am I missing something? I realize that this method is not ideal since it can break the log chain..
Thanks!
