How is log data allocated in log files?

• SQL ORACLE

  One Orange Chip

  Points: 27807

  More actions

  March 13, 2012 at 2:25 pm

  #270004

  I noticed a database has the following configuration in its log files:

  It has two log files.

  One has initial size 300 MB with 10% increment auto-growth;

  Another has initial size 0 MB with unchecked auto-growth;

  What may happen if this database is manipulated?

  It is not an interview question, it is a real issue in my work.

  Many thanks for any input in advance.

• Gail Shaw

  SSC Guru

  Points: 1004504

  More actions

  March 13, 2012 at 2:28 pm

  #1458847

  SQL uses the first log file, then the second, then goes back and reuses the first, then the second, etc. If both files are full and no VLFs are reusable, it will grow the file with autogrow enabled and use the newly allocated space.

  p.s. There are few good reasons for two or more log files.

  Gail Shaw
  Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
  SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

  We walk in the dark places no others will enter
  We stand on the bridge and no one may pass