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

[DLu]

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

[MAK]

http://forums.databasejournal.com/sh...ghlight=shrink

[DLu]

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

[MAK]

try doing this either in single user mode or during off hours when there are no connections or transactions.

[skhanal]

The log may not be smaller than 10 MB. Try running

dbcc shrinkfile(ABC_log)

[DLu]

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

[rmiao]

Seems the transaction is not committed untel you kill the process.

[DLu]

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

[rmiao]

Can try 'dbcc opentran'.