SQL Server 2017 transaction log does not shrink logfile after rebuild index

Question

Hello,

I have 10GB database and i have configured transaction log shipping. My rebuild indexes job executed indexes fillfactor changed from 100 to 70 and transaction logfile has growth 60GB.

I have tried so many things to shrink such as

Destroy log shipping configuration bring simple recovery mode and shrink not release also database has above 6000 virtual log file,
I have get full backup and transaction log backup.
I have get full backup and restore for new database.
I remember when I configuring log shipping I have set 72 hour "delete files older than". I don't think because of this log can not shrink, but maybe it can be reason after 72 hour later these virtual logs LSN can be expired and I can shrink.

Also DBCC LOGINFO shows except of 4 files every virtual log file has create LSN.

Answer 1

Transaction log VLFs do not expire, they get cleared after a Log backup. So the setting in retention of 72 hours will not make a difference of whether the VLFs clear to 0 instead of 2 on status.

You may need to issue a CHECKPOINT in your database and then see if that clears the status to 0, then you can shrink the log file.

Log backups are the only way to clear the status. The other way is to clear the log, is to change the Recovery Model to SIMPLE, but then to get FULL back you would need to change it to FULL and do a FULL Backup so that you establish the FULL recovery model. (This is not the way I would recommend for other reasons, so use this as a last resort, be patient for the log to clear after the log backups)

But if there is an open transaction at the end of the log, then it will not clear that VLF, so you basically have to wait for the transactions to wrap around to the front of the log and after the next log backup if there are no open transactions left in the end of the log then it will clear.

Answer 2

The physical SIZE of the log file never shrinks unless you manually shrink it.

If your database is set to "Full recovery", the log will grow until a log backup is complete. It will then mark the log data backed up INTERNALLY as available for reuse. It does not change the size of the physical file.

Unless you need "point in time" recovery, you can usually set the database to "Simple recovery". This will mark log space for reuse as soon as the transaction is complete.

See:

https://learn.microsoft.com/en-us/sql/relational-databases/backup-restore/recovery-models-sql-server?view=sql-server-ver15

Answer 3

Thanks, dears i understand but my problem completely different. As i told i already set simple take backup, set full take level 0 take tx log. I did all what you described. But somehow SQLServer doesnt not clear vlf thatswhy not shrink. Below is output take full backup, tx log backup and shrink.

BACKUP DATABASE [XYZ] TO DISK = N'L:\BACKUP\XYZ\XYZ.bak' WITH NOFORMAT, NOINIT, NAME = N'XYZ-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO

checkpoint

BACKUP LOG [XYZ] TO DISK = N'L:\BACKUP\XYZ\XYZ.trn' WITH NOFORMAT, NOINIT, NAME = N'XYZ-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO

USE
XYZ
GO
DBCC SHRINKFILE (N'XYZ_LOG' , 1)
GO

USE
master
GO
ALTER DATABASE XYZ MODIFY FILE ( NAME = N'XYZ_LOG', SIZE = 4096MB, FILEGROWTH = 256MB )
GO

60 percent processed. 
70 percent processed. 
80 percent processed. 
90 percent processed. 
100 percent processed. 
Processed 7953125 pages for database 'XYZ', file 'XYZ_LOG' on file 1. 
Processed 115866 pages for database 'XYZ', file 'XYZ_LOG2' on file 1. 
BACKUP LOG successfully processed 8068991 pages in 168.457 seconds (374.214 MB/sec). 
Cannot shrink log file 2 (XYZ_LOG) because of minimum log space required. 

(1 row affected) 
DBCC execution completed. If DBCC printed error messages, contact your system administrator. 
Msg 5039, Level 16, State 1, Line 18 
MODIFY FILE failed. Specified size is less than or equal to current size. 

Completion time: 2022-04-25T18:29:11.3225585+03:00

Answer 4

Hi microracl,

Welcome to Microsoft Q&A.
It seems the status of VLF is active (status=2), which means they cannot be truncated.
Please use the query to check it there are any open-running or uncommitted transactions:

SELECT [s_tst].[session_id],  
[database_name] = DB_NAME (s_tdt.database_id),  
[s_tdt].[database_transaction_begin_time],   
[sql_text] = [s_est].[text]   
FROM sys.dm_tran_database_transactions [s_tdt]  
INNER JOIN sys.dm_tran_session_transactions [s_tst] ON [s_tst].[transaction_id] = [s_tdt].[transaction_id]  
INNER JOIN sys.dm_exec_connections [s_ec] ON [s_ec].[session_id] = [s_tst].[session_id]  
CROSS APPLY sys.dm_exec_sql_text ([s_ec].[most_recent_sql_handle]) AS [s_est];  

Msg 5039, Level 16, State 1, Line 18
MODIFY FILE failed. Specified size is less than or equal to current size.

And DBCC SHRINKFILE doesn't shrink a file past the needed stored data size. For example, if 7 MB of a 10-MB data file is used, a DBCC SHRINKFILE statement with a target_size of 6 shrinks the file to only 7 MB, not 6 MB. Please check the doc for more details.

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.

Answer 5

The error you are getting is:

 Cannot shrink log file 2 (XYZ_LOG) because of minimum log space required. 

This is because you are running the command:

DBCC SHRINKFILE (N'XYZ_LOG' , 1)

The log file cannot be shrunk to 1mb. You must put in a bigger number than 1.