Cannot Shrink Log File

• Sameer Raval

  Hall of Fame

  Points: 3214

  More actions

  February 7, 2006 at 12:44 pm

  #103077

  I have job that runs for long time 

  which takes full db back up, the step that runs prior to backup is

  BACKUP Log  Alpha WITH TRUNCATE_ONLY

   DBCC SHRINKFILE(Alpha_log, 150)

  Now this step  shows

  Cannot shrink log file 2 because requested size is larger than the start of

  the last logical log file

  What does it mean? How to fix this ?

  Thanks in advance ,

   

   

  Kindest Regards,

  Sameer Raval [Sql Server DBA]
  Geico Insurance
  RavalSameer@hotmail.com

• Paul Mu

  SSCertifiable

  Points: 5547

  More actions

  February 7, 2006 at 3:38 pm

  #619427

  Is the 150MB that you specified greater than the current log file size that you are trying to shrink?

• Cath Trimble

  SSCarpal Tunnel

  Points: 4231

  More actions

  February 8, 2006 at 2:32 am

  #619453

  Hi Sameer

  Try running DBCC LOGINFO against your database.  This will show the status of the virtual log files in your log.  If the status of a VLF is not 0, then it's still active and can't be truncated.   You could then look for which process is holding the transaction open.

• jts2013

  Hall of Fame

  Points: 3226

  More actions

  June 26, 2009 at 6:51 am

  #1015978

  Hi there,

  I am having a similar problem with a log file, where the database is log shipped. Therefore, I'm pretty certain that the reason I can't shrinkl the log is becuase log shipping is several hours behind today.

  But I was interested by Cath's comment "You could then look for which process is holding the transaction open" - I was wondering how this would be done and how it would indicate that it was log shipping that was the problem.

  Cheers, John