Why the Shrink Log File operation may not effectively remove space from the log file?
There are many reasons that cause that the Shrink Log File operation may not effectively remove space from the log file as the following:
1. The log file may not shrink if it has not been backed up recently.
Regular log backups help truncate the log file and make space available for reuse. Without regular log backups, the log file may grow and not shrink even after performing the Shrink Log File operation as in your case.
So Make sure to schedule regular log backups to keep the log file size in check as mentioned at Create Transaction LOG BACKUP Maintenance Plan in SQL Server
Besides that, you can create an on-demand Backup log as below
BACKUP LOG 'DBName' TO DISK='Backup Path\log.bak'
Then perform the shrink operation on the log file
DBCC SHRINKFILE ('DBName_log', EMPTYFILE)
2. There isn't enough free space
If there isn't enough free space, the shrink operation can't make the file smaller. The log file is often the one that doesn't shrink properly. This happens because the log file hasn't been truncated.
To fix this, you can either change the database recovery model to SIMPLE
and then try shrinking the file again using the DBCC SHRINKFILE
operation by using the below query.
use DBName
go
alter DBName set recovery simple
go
dbcc shrinkfile('DB_log',100)
go
alter database DBName set recovery FUll
go
Check also, How to Shrink a Transaction Log File Maintenance Plan in SQL Server
3. Log File Size Limit
The log file may have reached its maximum configured size. In some cases, the log file growth is restricted by a maximum size limit defined for the file. If the log file has reached this limit, it cannot be further shrunk until the limit is increased or altered.
Check the maximum file size configuration for the log file and consider adjusting it if necessary.
Note: Shrinking log files should be done judiciously and in accordance with best practices, as it can have performance implications, read also The transaction log for database SharePoint_Config is full due to LOG_BACKUP