How to shrink an SQL database?

[jherr1971]

My service hosted at discountasp.net uses a MS SQL 2000 database. Many transactions are involved with this database inclusive deleting expired messages. I delete about 1000 rows in my database daily.

But my database keeps growing (currently over 100MB). One day I deleted over 2000 rows of data and the size of the database didn't shrink a bit.

After a while I figured that I have to perform a SHRINKDATABASE to gain back all deleted space. I've used this approach:

DBCC SHRINKDATABASE (database_name, 20)
GO

and get following error message:

Warnings: --->
W (1): Cannot shrink log file 2 (DB_54121_Log) because all logical log files are in use.

Thanks for any help in this issue.

[Danny]

There might be some open transaction when you are trying to perform the operation. Try

dbcc opentran (database_name)

to make sure there is no active open transactions and perform the DBCC SHRINKDATABASE again.

[Aristotle]

The database may need to be single user mode to shrink successfully.

[jherr1971]

Thanks Danny.

I ran dbcc opentran (database_name) and got following message:

W (1): No active open transactions.
W (2): DBCC execution completed.

And right after that I again run DBCC SHRINKDATABASE (database_name, 20) but with the same result as described in my initial entry above.

I also wasn't able to find any good explanation about this Online.

Any help is greatly welcomed.

[jherr1971]

Thanks Lukas. Any help is very welcome.

Can you explain to me how to establish a single user mode? As you can tell, I'm a newbie in all this stuff. I'm very good in access databases, though.