question

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.

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://docs.microsoft.com/en-us/sql/relational-databases/backup-restore/recovery-models-sql-server?view=sql-server-ver15

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

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.

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.

USE [master]
GO

 /****** Object:  Database [XYZ]    Script Date: 26.04.2022 17:40:31 ******/ 
 CREATE DATABASE [XYZ] 
  CONTAINMENT = NONE 
  ON  PRIMARY  
 ( NAME = N'XYZ', FILENAME = N'E:\DATA\XYZ.mdf' , SIZE = 11106624KB , MAXSIZE = UNLIMITED, FILEGROWTH = 65536KB ) 
  LOG ON  
 ( NAME = N'XYZ_LOG', FILENAME = N'F:\LOG\XYZ_LOG.ldf' , SIZE = 73641984KB , MAXSIZE = 204800000KB , FILEGROWTH = 262144KB ),  
 ( NAME = N'XYZ_LOG2', FILENAME = N'F:\LOG\XYZ_LOG2.ldf' , SIZE = 3416064KB , MAXSIZE = 204800000KB , FILEGROWTH = 65536KB ) 
 GO 
     
 IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled')) 
 begin 
 EXEC [XYZ].[dbo].[sp_fulltext_database] @action = 'enable' 
 end 
 GO 

Thanks for posting your db script. You need to review your log file sizes (size, filegrowth, maxsize) and the goal of your shrinking file.

Remove the second log file not used (SQL server will not use it as you are expecting).

ALTER DATABASE [XYZ] REMOVE FILE XYZ_LOG2

Let's see whether you can move forward with your actions.

 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
 --
 checkpoint
    
 USE 
 XYZ 
 GO
 DBCC SHRINKFILE (N'XYZ_LOG' , 4096) 
 GO
 --
 USE 
 XYZ 
 GO
 DBCC SHRINKFILE (N'XYZ_LOG2' , 1) 
 GO
    
 USE 
 XYZ 
 GO 
 ALTER DATABASE XYZ MODIFY FILE ( NAME = N'XYZ_LOG', SIZE = 4096MB, FILEGROWTH = 256MB ) 
 GO
    
 ALTER DATABASE XYZ  REMOVE FILE XYZ_LOG2; 
 GO
    
 10 percent processed.
 Processed 1364888 pages for database 'XYZ', file 'XYZ' on file 1.
 20 percent processed.
 30 percent processed.
 40 percent processed.
 50 percent processed.
 60 percent processed.
 70 percent processed.
 80 percent processed.
 90 percent processed.
 100 percent processed.
 Processed 9820817 pages for database 'XYZ', file 'XYZ_LOG' on file 1.
 Processed 574365 pages for database 'XYZ', file 'XYZ_LOG2' on file 1.
 BACKUP DATABASE successfully processed 11760070 pages in 231.959 seconds (396.085 MB/sec).
 10 percent processed.
 20 percent processed.
 30 percent processed.
 40 percent processed.
 50 percent processed.
 60 percent processed.
 70 percent processed.
 80 percent processed.
 90 percent processed.
 100 percent processed.
 Processed 32772 pages for database 'XYZ', file 'XYZ_LOG' on file 1.
 Processed 8191 pages for database 'XYZ', file 'XYZ_LOG2' on file 1.
 BACKUP LOG successfully processed 40963 pages in 1.257 seconds (254.587 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.
 Cannot shrink log file 3 (XYZ_LOG2) 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 27
 MODIFY FILE failed. Specified size is less than or equal to current size.
 Msg 5042, Level 16, State 2, Line 30
 The file 'XYZ_LOG2' cannot be removed because it is not empty.
    
 Completion time: 2022-04-26T22:50:31.9897663+03:00

Not changing anything i have tried before but now again i tried again same vlf show status still 2 thatswhy i cannot shrink, drop.
What you have done when you faced before ?

Here is another thing to try. It's quite wild, and if Ben calls out "DON'T DO IT", Ben is right. Thus, don't do this until, Ben has given go ahead.

First you create a database snapshot of this database:

CREATE DATABASE snap ON (NAME = 'snap', FILENAME = 'E:\Data\Snap.mdf') AS SNAPSHOT OF YourDB

Next, possibly you should perform some update in the source database. Adding or updating a single row should do. (And I am not sure that it is required.)

Next you revert to the snapshot:

RESTORE DATABASE YourDB FROM DATABASE_SNAPSHOT = 'snap'

At least it used to be the case when you revert from a database snapshot, the transaction is thrown away and is replaced with one that is mere 0.5 MB size. Thus, you should immediately grow it to a reasonable size.

Whether this will actually work in your case, I don't know.