none
"Cannot shrink log file because logical files are in use"

    Question

  • Dear users,

    Our CRM got an additional feature from C360, which is a group calendar. The weird thing is, the C360 has a very very big log_file; its size is triple the size of the C360_data file itself. Therefore I'm trying to run a script to shrink the file.

    Apparently I coudln't run the shrink; it keeps saying "Cannot shrink log file because logical files are in use", even tho I already stopped any C360 activities and even I was on single-user connection. I tried running  the DBCC OPENTRAN, but it said there was no open active transaction..

    Can anyone please help me on this one? Thanks in advanced!

    -Elisabeth


    Friday, November 02, 2007 5:32 PM

Answers

  • The message "Cannot shrink log file because logical files are in use" refers to the way SQL Server manages the space within the log file, and can still occur without any open transactions.

     

    From what I remember the sequence that works on SQL 2000 (and I expect would be similar on SQL 2005) is:

    Backup / truncate the log

    Run DBCC SHRINKFILE on the log

    Backup / truncate the log again

    Run DBCC SHRINKFILE on the log again

     

    There's an article somewhere in SQL Books online / MSDN that purports to explain how this works

    Friday, November 02, 2007 7:09 PM
  • xalgelx1985  Simple Recovery is not a solution for a production enviroment.  That's ok for DEV environments.   Aside from "point in time" restores, there are performance issues that are involved.


    Curtis J Spanburgh

    Wednesday, June 06, 2012 1:57 PM

All replies

  • The message "Cannot shrink log file because logical files are in use" refers to the way SQL Server manages the space within the log file, and can still occur without any open transactions.

     

    From what I remember the sequence that works on SQL 2000 (and I expect would be similar on SQL 2005) is:

    Backup / truncate the log

    Run DBCC SHRINKFILE on the log

    Backup / truncate the log again

    Run DBCC SHRINKFILE on the log again

     

    There's an article somewhere in SQL Books online / MSDN that purports to explain how this works

    Friday, November 02, 2007 7:09 PM
  • Thank you. The shrink was successful.
    Monday, November 05, 2007 4:57 PM
  • I was able to get this solution to resolve the same issue for me in SQL 2k5.

     

    I wanted to post my message\sqlstate codes for others to find:

    Full Message: 

    Cannot shrink log file 2 (MimosaExchangeItem_01_LOG) because all logical log files are in use. [SQLSTATE 01000] (Message 9008)  DBCC execution completed. If DBCC printed error messages, contact your system administrator. [SQLSTATE 01000] (Message 2528).  The step succeeded.

     

    Thanks for the help. 

    JP

     

    "Insanity: doing the same thing over and over again and expecting different results" - Einstein

    Tuesday, November 27, 2007 5:17 PM
  • Some more specifics.

     

    Backup Log c360 TO DISK='E:\C360.TRN, TRUNCATEONLY'

    SELECT * FROM SYSFILES

    'NOTE: This shows you the fileid of the transaction log or LDF file.

    It should be 2.

    DBCC SHRINKFILE(2,TRUNCATEONLY)

    DBCC SHRINKIFLE(2,50)

    BACKUP LOG TO DISK='E:\C360.TRN'

    Now run:

    Select * from sysfiles

    and check out the file size.

     

    Might as well create a SQL Job and schedule it.

    /:>

     

     

     

    Tuesday, December 04, 2007 7:41 AM
  • Yes.  I was stuck.  Nothing worked.  I tried your method.  It worked right-away.  YES!!! Thank you. -Mark

     

    Sunday, June 08, 2008 4:53 AM
  • hehehe guys it's so easy!!!!!
    all u have to do is doing this steps and u will shrink ur database form enterprise manager and query analyzer
    1. open enterprise manager.
    2. right click on the database u wanna shrink it.
    3. click on properties.
    4. from the data properties go to options.
    5. in the middle u will see recovery model make it "simple" then click on "ok" and try to shrink the database.
    it works 100%
    Sunday, April 12, 2009 9:47 AM
  • xalgelx1985 - this is fine if you only need simple recovery, but is not sufficient for users that need to be able to recover to a point in time after their last back up. -
    Scott Sewell, CustomerEffective | http:\\blog.CustomerEffective.com | Twitter:@ScottSewell
    Sunday, April 12, 2009 9:16 PM
  • Agreed Scott.   After all , what kind of CRM deployment only needs simple recovery.    

    Just a note, it's a good Idea to do a backup of the transaction log first.  Using the truncate only option does not create a transaction backup that can be restored or so says Books Online.

    So a backup should commit the data in the log to the database.  Then if you have to get the disk space back you can shrink the log.

    I would not put the databases in simple recovery on a busy system and I would get the Server Config down right.

    I don't want to be a SQL Snob but I have seen some really bad deployments and it's not fair to the adminstrators that have to deal with them after the VAR has left the building.
    /:<
    Tuesday, June 16, 2009 8:10 PM
  • And further to Curt’s comment:

    Event though if you are still in full recovery mode by using “truncate only” option you are losing ability to recover in time. Or to get to the point: you would need to backup entire database right after you issue a statement with “truncate only” option to preserve recovery ability at point in time.

     

    Monday, July 06, 2009 7:29 PM
  • Exactly.  These post are not a maunual to SQL.  So it's best to know your Books onLine resource.

    For instance you cannot restore a log from a truncated backup like Bob says above.

     

    And shrinking the transaction log without backing up the log first does not clear the "Dirty buffers" and write that data to the database.

    So don't shrink every night.

    Just make a job for when you need it.

    And Note that Books online says to only do this if you have an emergency.  Like your drive filling up.  

    And frequent Transaction log backups will help keep the log file a decent size.  In some environments, it may not since some SQL based apps may generate a need for growth.

    /:>

     

    Monday, July 06, 2009 8:09 PM
  • xalgelx1985  Simple Recovery is not a solution for a production enviroment.  That's ok for DEV environments.   Aside from "point in time" restores, there are performance issues that are involved.


    Curtis J Spanburgh

    Wednesday, June 06, 2012 1:57 PM