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

    Question

  • SQL 2005 9.0.2153 EE English
    Database has recovery model = Full

    I have job which is executed at night time. It executes these main tasks (in order of execution)
    1. Renames transaction log backup of previous day.
    2. Executes maintenance plan (check database -> rebuild indexes -> update statistics)
    3. Makes fake backup of transaction log (to remove it later).
    4. Shrinks log (dbcc shrinkfile(...,0, TRUNCATEONLY).
    5. Deletes fake backup from step 3.
    6. Makes full backup of database.

    There is another job that executes every 15 minute at work time which backups transaction log.

    Step 4 finishes with message "Cannot shrink log file ... because all logical log files are in use". And log size remains unchanged.

    If I try to shrink log at work time using same dbcc shrinkfile I don't get message and log becomes small.

    I added 2 steps with checkpointing. Now job executes these steps:

    1. Renames transaction log backup of previous day.
    2. Executes maintenance plan (check database -> rebuild indexes -> update statistics)
    3. Checkpoints database
    4. Makes fake backup of transaction log (to remove it later).
    5. Checkpoints database
    6. Shrinks log (dbcc shrinkfile(...,0, TRUNCATEONLY).
    7. Deletes fake backup from step 3.
    8. Makes full backup of database.

    But message occures yet and log is not shrinked.
    What I need to do to shrink log automatically. I need to shrink it because it grows very much after maintenence plan.

    Thursday, October 05, 2006 9:23 AM

All replies

  • I suggest you take a look at this article.
    http://www.karaszi.com/sqlserver/info_dont_shrink.asp


    • Proposed as answer by SystumsAdmen Thursday, September 22, 2011 8:55 PM
    Saturday, October 07, 2006 4:03 AM
    Moderator
  • I am not certain what commands would be equivalent, but when I have trouble with the [all logical files in use] message, I use the applet All Tasks, Shrink Database, Files - with the Compress And Truncate option.  This seems to reorganize the data spread throughout the logical files and then does a decent truncate.  I agree with what the MVP is saying, but after I perform a huge data cleanup over a weekend, our log file grows from a typical sub-1GB size to nearly 30GB.  That will choke down a t-log backup in a heartbeat and cause loss of disk space.  This is in reference to SQL Server 2000.
    • Edited by M Ramsey Monday, February 09, 2009 3:08 PM Spell; server version
    • Proposed as answer by xangelx1985 Sunday, April 12, 2009 9:42 AM
    Monday, February 09, 2009 3:04 PM
  • 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%
    • Proposed as answer by Codesleuth Wednesday, May 27, 2009 3:07 PM
    Sunday, April 12, 2009 9:43 AM
  • Hi Alexey,

    My data ware housing team send me a same error message what ever you got It.

    In this situation I suggest you to take 10 mins break between your 2nd and 3rd step. I am sure your problem will be solved.

    Alexey, one more thing I want to suggest you that if its your daily process, then don’t shrink your log file. In your case shrinking is not giving any benefits to you, but it takes resources from your server and finally in future your system will go slow.
    The reason is that when you shrink any file, the system fragmentation level become high compare with previous and if its your daily process then you can think about that.


    Regards
    Deepak Goyal

    • Proposed as answer by Deepak_Goyal Monday, April 13, 2009 6:33 AM
    Monday, April 13, 2009 6:33 AM
  • After reading in a lot of of forums, the solution of xangelx1985 is the first solution that actually works.
    Thursday, May 07, 2009 2:49 PM
  • I was going out of my mind until I saw your post.

    XANGELX1985.....THANK YOU!!

    Can you explain why this happens? 
    Tuesday, May 19, 2009 6:52 PM
  • it's not good practice to change recovery mode of production database
    in this case you can't save logs and in future you'll be able to restore database only up to recent full+differential backup date with possibility of data loss between backup and database failure date.

    regards

    Thursday, May 21, 2009 6:58 AM
  • XANGELX1985 You're the best. after 2 days of searching this was the best solution, everybody is a specialist in theory but nobody in practice. This is great. Thank you for the solution has definitely solved a big issue for me. 500 MB db and 10GB log files. 

    Cheers,
    Friday, November 27, 2009 1:03 PM
  • I agree with k0mrad: simple recoverymode prevents you from recovering in case of databaserecovery until the last log-backup. There is a REASON to use full recovery instead of simple recovery.
    As is suggested by oj(the link, that is) consider NOT shrinking your logfile.
    Greetz, FreeHansje
    Thursday, December 10, 2009 8:00 AM
  • Alexey,

    I'm curious what exactly are you doing in the step #4 "Makes fake backup of transaction log (to remove it later)"? If that's a backup WITH COPY_ONLY then it doesn't truncate transaction log... If that's a a regular backup and you delete it then you are breaking LSN sequence and your transaction log backup is basically useless after that point.
    As has been mentioned before, switching from Full to Simple recover should be used as a last resource.
    In most cases you won't be able to truncate log if there are long running uncommitted transactions...
    Maybe if you move step #6 to run after step #8 you would have more luck?
    On another hand, if your log grows to the size where you have to shrink it, then maybe you should just leave its size big? Disk operations are kind of expensive.

    Regards,
    Akim

    Thursday, December 10, 2009 10:00 PM
  • Thank you very much. It really helps.
    Jana
    Wednesday, January 13, 2010 1:29 AM
  • Greetings all. Sorry to jump in on this thread so late, but I'm having this same issue, and NOTHING is working. I've tried every solution here to no avail.

    We are on a 2005 SP2 box, and this particular DB is in 2000 Compatability Mode (CM).

    In addition to everything here, I've tried:

    1) Creating a new TLog file, and dropping the original. Not allowed.
    2) Changing the CM to 2005. Didn't help.
    3) Actually changing the file size using ALTER DATABASE. Not allowed to specify less than it's current size.

    This is getting scary as I've got a 60 GB TLog, and need to reclaim this space. I have to shrink TLogs weekly, but have never encountered this until now.


    Any other ideas? I'm really at the point of considering PSS, but wanted to make one last effort here.

    Thanks!


    TIA, ChrisRDBA
    Friday, February 19, 2010 2:20 PM
  • ChrisRDBA -

    Did you try xAngelX1985's solution above?

    It just worked for me.  After shrinking it I changed the recovery model back to "Full".

    Mark
    Friday, February 19, 2010 3:02 PM
  • Yes, I've tried everything.
    TIA, ChrisRDBA
    Friday, February 19, 2010 3:42 PM
  • My view is if you understand the consequence of setting the database to SIMPLE then why not do it. Obviously this may impact other things such as log backup, logshipping, mirroring etc but a good DBA should already know this and be prepared to sort it out or prepeare for the consequences of the change

    in my case i had to remove an extra Database Tlog file so had now choice as there were inactive transactions in the log that prevented me from shrinking and emptying

    I ran the following to delete my extra log file

    USE [master]
    GO
    ALTER DATABASE [nameofdb] SET RECOVERY SIMPLE WITH NO_WAIT
    GO

    USE [nameofdb]
    GO
    DBCC SHRINKFILE (N'nameofdb_extralog' , EMPTYFILE)
    GO

    USE [nameofdb]
    GO
    ALTER DATABASE [nameofdb]  REMOVE FILE [nameofdb_extralog]
    GO

    USE [master]
    GO
    ALTER DATABASE [nameofdb] SET RECOVERY FULL WITH NO_WAIT
    GO

    I had to break mirroring to do this so hey ho, just going to go and set it up again ;-)

    Wednesday, April 21, 2010 10:29 AM
  • My view is if you understand the consequence of setting the database to SIMPLE then why not do it. Obviously this may impact other things such as log backup, logshipping, mirroring etc but a good DBA should already know this and be prepared to sort it out or prepeare for the consequences of the change

    in my case i had to remove an extra Database Tlog file so had now choice as there were inactive transactions in the log that prevented me from shrinking and emptying

    I ran the following to delete my extra log file

    USE [master]
    GO
    ALTER DATABASE [nameofdb] SET RECOVERY SIMPLE WITH NO_WAIT
    GO

    USE [nameofdb]
    GO
    DBCC SHRINKFILE (N'nameofdb_extralog' , EMPTYFILE)
    GO

    USE [nameofdb]
    GO
    ALTER DATABASE [nameofdb]  REMOVE FILE [nameofdb_extralog]
    GO

    USE [master]
    GO
    ALTER DATABASE [nameofdb] SET RECOVERY FULL WITH NO_WAIT
    GO

    I had to break mirroring to do this so hey ho, just going to go and set it up again ;-)


    I didn't change it to SIMPLE because I understand the consequences as well as I do, and was unwilling to accept the consequences of making this switch.

    The one (and only) thing I was able to do do remedy this issue was to detach the DB, then do a single file attach (thereby creating a new TLog).

     


    TIA, ChrisRDBA
    Wednesday, April 21, 2010 2:08 PM
  • Hi,

    We had a simliar situation on a production database with log shipping enabled, so we did not want to use the SIMPLE mode option. The log file would not shrink because there was an open transaction left by a poorly written application. DBCC OPENTRAN identified the SPID. We killed the SPID and were able to shrink the log.

    Cheers

     

    • Proposed as answer by inetcnslt Thursday, May 12, 2011 6:29 PM
    Monday, November 29, 2010 2:43 PM
  • This is what was causing our problem. The log was being backed-up, but the open trans stopped it from shrinking. It had gotten to the point where the log was too big to back up to disk. I used DBCC opentran to find the transaction and killed it. After that I was able to shrink the logfile back to normal.
    Thursday, May 12, 2011 6:31 PM
  • Thank You Very Much

    It's the easy way 

    Friday, March 09, 2012 9:22 PM
  • xangelx1985

    You rock!!

    • Proposed as answer by NareshChounde Monday, October 15, 2012 11:51 AM
    • Unproposed as answer by NareshChounde Monday, October 15, 2012 11:51 AM
    Friday, September 21, 2012 4:41 AM