Sql server logfile problem

Question

I have a database on SQL SERVER 2014 which weighs 237Gb and the log has 307Gb. Whenever I try to reduce the size (graphically or with TSQL) of the log, the size increases instead. How to proceed in this case?

Answer 1

Hi @developpement info ,

If you want to reduce the size of log file, you need to shrink log file. In order to shrink transaction log, please follow the steps below:

1. Perform full backup of database
2. Perform transaction log backup
   sometimes, we have to perform multi transaction log backup before the transaction log could be shrank.
3. Shrink transaction log
   if the transaction log cannot be shrank, 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. Then please try step 2 and 3 again.

---

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Answer 2

What does the query

select name, recovery_model_desc, log_reuse_wait_desc
from sys.databases

return for the database in mind?
Is the database in full recovery mode and are you performing frequently log backups?