We have a database which is currently 250 GB in size. The data is close to 23 GB but the log has grown to **225 GB**. Database is in Full recovery and there were no transaction log backups due to which it has ballooned to this size (I know where it went wrong :(). Now there is just 20 GB left on the data drive and there is no space for taking a transaction log backup. If we change recovery model to simple and shrink the transaction log would it result in data loss ? Or is there a better way to shrink this overgrown log file ? Please help ! It's a little urgent :(
Changing to simple recovery will not result in data loss. However, it does mean that if you were to have any type of outage and wanted to restore the database to a point in time, you would absolutely be unable to. In your situation, I would change to simple recovery, truncate the log and shrink it. Shift it back to full recovery. Immediately take a full backup and then start taking log backups on a scheduled basis in compliance with your Recovery Point Objective (RPO).