dbcc shrinkfile failing

[mkal]

I am running sql 2000 and have a hard drive space issue. One of the log files has grown to large and I need to shrink it. I am running the following script.

(The database in question has the Recovery Mode set to full)

backup log dbName with truncate_only
go
dbcc shrinkfile ('dbName_Log', 1000)

However I get the following message: Cannot shrink log file 2 (dbName_Log) because all logical log files are in use.

I don't understand I thought that truncate_only truncated all the inactive portion of the log how can they all still be in use and what can I do to fix it?

Thanks in advance!

 

[ptheriault]

try this,

DBCC SHRINKFILE (db_log_name, truncateonly)

- Paul

- If at first you don't succeed, find out if the loser gets anything.

 

[mkal]

same error messgae: Cannot shrink log file 2 (dbName_Log) because all logical log files are in use.

What now?

 

[ptheriault]

Right click on the database select properties
and set it to simple recovery. This will truncate the log on next checkpoint.


- Paul

- If at first you don't succeed, find out if the loser gets anything.

 

[mkal]

Set recovery model to simple

ran checkpoint in QA
ran dbcc sqlperf(logspace)
output:
DatabaseName Logsize Log Space Used Status
BDName 2438.2422 97.960747 0

This isn't making any sense

 

[ptheriault]

If the transaction log won't shrink then you have open transactions. Is this a prod database?

- Paul

- If at first you don't succeed, find out if the loser gets anything.

 

[mkal]

Yes it is and that was it an open tran. Some process that was hung since yesterday afternoon - some days you just miss the obvious.

Again thanks for the quick reply!
Mike