Sql-server – Reclaim Transaction Log space of SQL Server

azure-vmdisk-spacesql servertransaction-log

I have a SQL Server databases for which log files have grown huge. I have log backup scheduled to run only once a day. But now all the disk space is used by these log files.

Can anyone help me figure out what could be possible ways to reclaim that used space again. Here's what I understood and aware off.

  1. I need to take more frequent log backups. As, SQL Server will internally reuse that logspace as and when required.
  2. I have DB's in Full Recovery Model and under AlwaysOn setup. Hence, if I want to shrink them, I'll need to first remove them from AlwaysOn and then put them in Simple Recovery mode and then Shrink the Log File to desired size limit.

Is there any other way apart from the two mentioned above. Can I create a new log files with fresh required size on some other drive and make my db's point to that new location. Is it a doable task? need more clarity on this.

Any inputs on this would be a great help.

Best Answer

Yes, take log backups more frequently. Is there a reason they are so infrequent? It seems like you have a lot of potential for losing data (not just in complete disaster). The log file will continue to grow until a backup is taken, because it will not re-use the space.

Related Question