How to shrink an SQL database?

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.

 

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.

 

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

 

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.

 

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.