Simon's SQL

SQL,DBA,tuning,Trouble Shooting,Performance

DBCC SHRINKFILE isn’t working even simple recovery mode.

Posted by Simon Cho on 07/15/2014

Log file Shrink has several dependency.

1. Log backup(or Checkpoint in simple recovery mode)

2. Replication

3. Mirroring

4. VLF

#1,2,3 can be check by this script

Declare @DBName varchar(255) = ‘test’
select log_reuse_wait_desc
from sys.databases where name= @DBName

So, most case, it’s ok.
But, only #4 can’t be checked by above script.

Please use this file to be able to check “StartOffset” for second VLF since LDF required minimum 2 VLFs.


If it’s too big, need to refresh VLF.






But, unfortunately, refresh VLF isn’t simple.

Ref. Log_reuse_wait (
0 = Nothing
1 = Checkpoint
2 = Log backup
3 = Active backup or restore
4 = Active transaction
5 = Database mirroring
6 = Replication
7 = Database snapshot creation
8 = Log Scan
9 = An AlwaysOn Availability Groups secondary replica is applying transaction log records of this database to a corresponding secondary database.
10 = For internal use only
11 = For internal use only
12 = For internal use only
13 = Oldest page
14 = Other (transient)

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s