Results 1 to 9 of 9

Thread: Cannot shrink log file 2 (ABC_Log) because all logical log files are in use....

  1. #1
    Join Date
    Mar 2003
    Posts
    383

    Cannot shrink log file 2 (ABC_Log) because all logical log files are in use....

    A small database ABC with data only 5 mb but its log is growing everyday around 20 mb. I want to shrink its size like for other databases on daily bases.

    1. backup log ABC with truncate_only
    2. DBCC SHRINKDATABASE (ABC, 10)
    got following error:
    <<Cannot shrink log file 2 (ABC_Log) because all logical log files are in use.>>

    with no_log also tried but have the same error when dbcc shrinkdatabase..
    any idea?

    thanks
    -D

  2. #2
    Join Date
    Sep 2002
    Location
    Fantasy
    Posts
    4,254

  3. #3
    Join Date
    Mar 2003
    Posts
    383
    I did the following

    use ABC
    go
    checkpoint --ok
    go
    backup log with truncate --error
    go
    dbcc shrink empty --error
    go

    when I changed to
    backup log ABC with truncate_only --OK

    dbcc shrinkdatabase(ABC, 10) --error in use

    dbcc shrinkfile(ABC_log, 10) --error in use

    -D

  4. #4
    Join Date
    Sep 2002
    Location
    Fantasy
    Posts
    4,254
    try doing this either in single user mode or during off hours when there are no connections or transactions.

  5. #5
    Join Date
    Nov 2002
    Location
    New Jersey, USA
    Posts
    3,932
    The log may not be smaller than 10 MB. Try running

    dbcc shrinkfile(ABC_log)

  6. #6
    Join Date
    Mar 2003
    Posts
    383
    Thanks MAK and Skhanal for your help.

    Actually, the log could be less than 10mb. After kill a process which was in 'sleep' mode for 10 hours, I was able to shrink the database log to only 2 mb.
    The issue is at that time, there is no 'running' process at all while erroring on 'in use' with dbcc shrinkdatabase. There is no application service on that machine. ODBC is the only connection from application to it.

    -D

  7. #7
    Join Date
    Sep 2002
    Posts
    5,938
    Seems the transaction is not committed untel you kill the process.

  8. #8
    Join Date
    Mar 2003
    Posts
    383
    Thanks rmiao.
    Is there a way to check a 'sleeping' processID to see if there is a un-committed transaction? Since dbcc inputbuffer(id) only tell us the last sql statement.

    -D

  9. #9
    Join Date
    Sep 2002
    Posts
    5,938
    Can try 'dbcc opentran'.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •