Hi @Avyayah ,
There are a number of reasons a log file can fill to extreme sizes. The most common one by far is that the database is in full recovery model, and Transaction Log backups are not happening fast enough. Next to that, it could be that you had a massive transaction happen such as a huge data import, rebuild all indexes, etc. These are logged and stay there until the .ldf file is backed up.
If the transaction log cannot be shrank and reused, please execute the following statement to check why the transaction log cannot be shrink.
SELECT log_reuse_wait_desc FROM sys.databases WHERE name='<database name>'
If the result is other than NOTHING, please perform corresponding operation.
To reduce the physical size of a physical log file, you must shrink the log file. This is useful when you know that a transaction log file contains unused space.
In order to shrink transaction log, please follow the steps below:
- Perform full backup of database
- Perform transaction log backup
sometimes, we have to perform multi transaction log backup before the transaction log could be shrank.
- Shrink transaction log. Right click “your database”->tasks->shrink->files->change file type to “log”->Choose “Reorganize Pages before releasing unused space”->Shrink file to ** MB.
Suggest you read the MS document Manage the size of the transaction log file and the blog Why is my SQL Log File Huge to get more information.
If the response is helpful, please click "Accept Answer" and upvote it, thank you.