Still Yet Another SHRINKDATABASE Question

[fal762]

Hello all,
I am trying to reduce the size of a SQL Server database after a major purge of old data. I have tried using:

DBCC SHRINKDATABASE (<db name>, TRUNCATEONLY)

And the result is the message:

Cannot shrink log file 2 (<db name>) because all logical log files are in use.

(1 row(s) affected)


As far as I know, I am the only one accessing the db and there are no transactions in progress. I'm also not concerned about the transaction log files as I figure a full backup should take care of them.

Does anyone know what SQL Server is complaining about and what to do about it?

 

[fredong]

first I would use sp_who to make sure nobody is in the database. Second, try to change it to a single user mode only and shrink the database again.

 

[JamesLean]

Before you shrink the file, you need to truncate the log. If you're not using the simple recovery model, then you have to backup the log to truncate it.

Check out this FAQ for more info:

Shrinking Databases and Logs - SQL 7 and Higher
faq183-1534

--James