SQL Server 2008 – Shrink database and truncate transaction log file

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

DBCC shrinkfile(‘db1’,1)

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’

output

log_reuse_wait_desc

————————————————————

LOG_BACKUP

(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.

DBCC Shrinkfile(‘db1_log’,1′)

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.

 

Leave a Reply

  

  

  


8 − 7 =