CODEWITHSUNDEEP
sql-serverdatabase-backupstransaction-log
Ken
Ken

Reputation: 2838

SQL Transaction Log Full when running ShrinkFile SQL Server

I am having an issue with DB Log Full and going through all the things trying to figure out why this is happening. I have of course implemented the backup here.

I run the backup commands for the shrink of the database log, the odd thing is that sometimes the script runs and at other times I get an error.

  USE [MYDB]; 
  BACKUP DATABASE [MYDB] TO DISK = 'L:\Backup\MYDB_DB_2023_01_05__T08_10_21.bak' ; 
  BACKUP LOG [MYDB] TO DISK = 'L:\Backup\MYDB_log_2023_01_05__T08_10_21.bak'  WITH INIT;  
  ALTER DATABASE [MYDB] 
  SET RECOVERY SIMPLE;  
  DBCC SHRINKFILE(MY_Log, 3000); 
  ALTER DATABASE [MYDB] 
  SET RECOVERY FULL;

I have no idea why I am seeing the error message below since I am running this to backup / truncate the Log. My DB Log size is set to 3GB & Autogrow with Limit 7GB, SQL Express version is 2017

Msg 3013, Level 16, State 1, Line 4 BACKUP DATABASE is terminating abnormally. [I don't know why this occurs]

Processed 894794 pages for database 'MYDB', file 'MY_Log' on file 1. BACKUP LOG successfully processed 894794 pages in 97.732 seconds (71.528 MB/sec).

(1 row affected) DBCC execution completed. If DBCC printed error messages, contact your system administrator.

(1 row affected) Msg 9002, Level 17, State 2, Line 4 The transaction log for database 'MYDB' is full due to 'LOG_BACKUP'.

/ Yes I know the Log is full that is why I am running this script! //

EDIT: Comment Requested Info.

SQL Query Result: SELECT [name], log_reuse_wait_desc FROM sys.databases;

Upvotes: 0

Views: 186

Answers (0)

Related Questions