Search code examples
sql-servertransaction-log

What is the best possible way to tackle SQL Server transaction logs filling up disk space?


I need the best shortest possible way to handle SQL Server transaction log files as my disk will be having a problem in saving them, in future. I don't know how to tackle this Low Disk Space issue, as my drive's free space is already in MB's.

Now, I don't know that whether the logs will be lost or affected if I move them to some other drive or will it effect the transactions or how to save the future logs depending on some previous transactions, if the disk space is completely full. Someone please help!!


Solution

  • Lets first take something off the table: do not delete or move any database mdf or ldf file. You'll end up corrupting and loosing the database.

    1. You need to investigate why is the log growing. Go read Factors That Can Delay Log Truncation. Follow the steps in the article to identify why is the log growing.

    2. If the reasons is anything else but 'LOG_BACKUP', post an update with the reason you discovered and we can take give further advise.

    3. If the reason is LOG_BACKUP then we can proceed. You have a database in non-SIMPLE recovery mode which is not being backed up correctly. You need to answer a question: Why is the database not in SIMPLE recovery mode? This is a business decision question so we cannot possibly know the answer.

    4. If you don't know the answer to above or if you realize that SIMPLE recovery mode is acceptable, then we can do the quick fix. Change the recovery model to SIMPLE then run DBCC SHRINKFILE to shrink the log.

    5. If you need a non-SIMPLE recovery model then you need to set up a proper log backup plan and start taking log backups. Read Transaction Log Backups and Use the Maintenance Plan Wizard. See See How to shrink the SQL Server Log to understand why you need to take repeated log backups until the SHRINKFILE is effective, due to the circular nature of the log.