Simon's SQL

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.

DBCC LOGINFO

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

But, unfortunately, refresh VLF isn’t simple.

http://www.sqlskills.com/blogs/kimberly/transaction-log-vlfs-too-many-or-too-few/
http://technet.microsoft.com/en-us/library/aa933049%28v=sql.80%29.aspx
http://www.brentozar.com/blitz/high-virtual-log-file-vlf-count/
http://sqlstudies.com/2013/08/26/the-effect-of-vlf-size-on-shrinking-the-log/

Ref. Log_reuse_wait (http://msdn.microsoft.com/en-us/library/ms178534.aspx)
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)