Shrinking Database problem

[homebrew]

I have a SQL 2000 database that I would like to shrink.

Recovery Model = Simple
Data file space allocated = 78 Gig
Space free = 41 Gig

Similar problems with Trans logs sometimes

As I understand it, I can't shrink the database below the allocated size. How do I get that space back ?

Any extra insights and explanations about this topic in general would be appreciated .... Thanks in advance.

[skhanal]

Did you try using DBCC SHRINKDATABASE, the size you can shrink to with this is not the allocated size but the size at the time of database creation. If the database has grown automatically then you should be able to shrink it.

If DBCC SHRINKDATABASE does not work, try to shrink file wise using DBCC SHRINKFILE

If this also does not help add a new file group with file sizes that would fit your database, then use DBCC SHRINKFILE with EMPTYFILE clause to shrink the original file, this will move the data to new files. After that you can drop the old files with ALTER DATABASE. See BOL for DBCC SHRINKFILE.

[homebrew]

I'm not sure of the original size .... I think that's the # under Properties, then Data Files, then the "Space Allocated" column. This database was created by restoring from another database.

So, if Database A is 75 Gig, then I restore it to Database B. Database B is now 75 G, but I clear a bunch of tables that I don't need on 'B', so that space allocated is still 75, but space used is 25 G, how do I free up the unused 50 G ?? When I go into the shrink file screen/Files/"Shrink file to", it says I can shrink down to 25 G minimum, but when I run it, nothing has changed.

[rmiao]

You can run 'dbcc shrinkfile' or shrink it in sql2k enterprise manager.