As part of daily monitoring task, we checked production server and found that log file was growing more frequently for some of databases compare to others. Its throwing transaction log file is full for database ‘DB1’ and so on.
As primary steps we took the log backup for the database using command
Backup log db1 to disk=’c:\backup\db1_lg.trn’
Now shrink the log file
After executing above commands still showing same size, ideally this commands should work and log file size should reduce.
Execute below query to confirm whether transaction log file is ready for reuse or not
select log_reuse_wait_desc from sys.databases where name = ‘db1’
(1 row(s) affected)
Again we had taken log backup and tried t shrink the log file but no effect then we tried one more option
1. Take Full backup, it may take time based on database size
Backup database db1 to disk=’c:\backup\db1_full.bak’
2. Take transaction log backup
Backup Log db1 to disk=’c:\backup\db1_log.trn’
3. After doing this we checked log file still it was showing same size. Now run the DBCC shrink transaction log file command.
Surprise to see the log size which reduced from 100 GB to 200MB.
Finally transaction log size is reduced and it worked fine.
Same situation or problem faced by one of my friend in his organization, what he did u know.
He checked whether any transaction is open or not then changed the recovery model of that database to simple and shrank log file and again back to full recovery model. After doing this log file size issue got resolved.
Note: I don’t recommend you to follow below steps that will affect to point in time restore of the database. Also you can lose your data.
ALTER DATABASE [Db1] SET RECOVERY SIMPLE WITH NO_WAIT
DBCC SHRINKFILE(DB1, 1)
ALTER DATABASE [DB1] SET RECOVERY FULL WITH NO_WAIT
As per organization policy, transaction log details were not much important to them as long as they have full backup. In this case, They should keep database in simple recovery model that will truncate log every time full backup is performed.