I have a server that is running extremely low on disk space and I dug into to determine if something is not functioning properly or if it simply is a healthy database that just happens to be growing.

I discovered that an ldf for a particular DB has reached 116gb (more than half the available space on the drive). However everything seems to be operating as it should in order to properly maintain the ldf size:

-Nightly Full backups
-Morning log backups

I also tried shrinking the ldf to free any unused space and got almost a single mb back. All things considered would this be a case where we just need to either A)increase disk space or B)move this DB's ldf to a different location?

Any feedback is greatly appreciated!

  • what is your recovery mode? and what is your backup command? – Ahmad Abuhasna Feb 4 at 18:05

Log truncation frees space in the log file for reuse by the transaction log. You must regularly truncate your transaction log to keep it from filling the allotted space. Several factors can delay log truncation. One of the most probable reason - you didn't make log backups. You need to backup your transaction log and after that you will be able to shrink it. If you don't need log backups you can switch database recovery model to simple and shrink the log. Other possible reasons are a bit more complicated (for example, you may have some issue with database mirroring or long running transactions). You can find more details here: https://docs.microsoft.com/en-us/sql/relational-databases/logs/the-transaction-log-sql-server?view=sql-server-ver15#FactorsThatDelayTruncation

| improve this answer | |

Have you checked log_reuse_wait_desc from sys.databases, and what it is shows?

You probably got to take appropriate action based on the result:

select name, log_reuse_wait_desc, recovery_model_desc
from sys.databases

Also, if the database set to FULL RECOVERY MODEL, you need to increase frequency of LOG Backups depending on Log generation, there is no other better way to avoid same issue in future.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.