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..
I did the following
backup log with truncate --error
dbcc shrink empty --error
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
try doing this either in single user mode or during off hours when there are no connections or transactions.
The log may not be smaller than 10 MB. Try running
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.
Seems the transaction is not committed untel you kill the process.
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.
DatabaseJournal Recent Articles