# Database Discussions > MS SQL Server 7/MS SQL Server 2000 >  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'.

----------

