Post reply

Cannot Shrink Log File

  • 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

  • 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?

  • 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.

  • 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

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply