Tran Log VLF 's - how exactly do they work?

• winston Smith

  SSCoach

  Points: 19500

  More actions

  August 2, 2012 at 8:17 am

  #259600

  Lets say i have a tran log with 4 VLF's.

  1st one is full, with no open transactions.

  2nd one is half full when i open a transaction and the rest are empty.

  I forget to close my transaction.

  All VLF's fill up until i reach the 1st one.

  Just before the Transaction log loops back to the first VLF, i have run a log backup and done a checkpoint.

  My Question is

  As there is an open transaction in VLF2, will the log backup and checkpoint i ran actually truncate the 1st VLF or will the tran log simply grow a bit more to accommodate further transactions?

• Suresh B.

  SSC-Insane

  Points: 22986

  More actions

  August 3, 2012 at 12:09 am

  #1521236

  No VLFs would be truncated.

  select log_reuse_wait_desc from sys.databases where name = '<dbname>'

  would return "ACTIVE_TRANSACTION"