SQL Server :The transaction log for database 'XYZ_DB' is full due to 'LOG_BACKUP'.

Question

Hello Team,

I have existing TLOG size is 80GB.And my total Hard disk is full.I need to create some space in my Hard disk.Whether i can copy the TLOG File into other Hard disk and try to run this process :

USE AxDB;
GO
-- Truncate the log by changing the database recovery model to SIMPLE.
ALTER DATABASE AxDB
SET RECOVERY SIMPLE;
GO
-- Shrink the truncated log file to 1 MB.
DBCC SHRINKFILE (D365DB_log, 1);
GO
-- Reset the database recovery model.
ALTER DATABASE AxDB
SET RECOVERY FULL;
GO

Please advise.

Regards
RK

Answer 1

Better do what the messages says: Run a log backup to release VLF for re-use.

Answer 2

First, the physical size of the log file never gets smaller unless you manually shrink it. The space INSIDE the file gets marked for reuse after a log backup.

Second, do you need full recovery? If so, you need to be doing regular log backups to keep the log file small. If not, you should just leave it as simple recovery. Do you need the ability to restore to a "point in time"?

Answer 3

Hi RohitKulkarni-6062,

In addition, please note that switching to the simple recovery model breaks the log backup chain. If the most recent full backup (before or after switching to simple) is corrupted, there is no way to restore it.
You can perform a log backup in full recovery mode, then run DBCC SHRINKFILE to shrink transaction log.
Please check this article which might help.

Best Regards,
Amelia

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.