1

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.

  • 3
    DONT follow the second point you mentioned its totally incorrect, I dont know where you read that, its simply blunder. Did you read This SE thread. For performing transaction log backup on AOAG please refer This Blog – Shanky May 9 '16 at 6:58
  • Why do you think you cannot shrink the log file while it is involved in an AG? – Shawn Melton May 9 '16 at 7:28
  • Just take the log backup, check the %log file usage using DBCC sqlperf(logspace) and shrink it. – SQLPRODDBA May 9 '16 at 7:42
  • Thank you All for your references. @Shanky:-I agree to that second point should not be an approach for this. Actually I am aware why my transaction log is growing and SQLPRODDBA:- I do check the logfile size before performing any huge transactions but the intention is to visibly seeing that space on that drive.Shawn: We can shrink the log file but it is not really releasing any space as the DB's are in full recovery mode and trans backup is happening only once. – MySQL DBA May 9 '16 at 8:36
1

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.

-3

Another solution would be to direct your transaction logs to a separate drive, and continue with the log backup or you could find the longest running transaction and kill it

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.