Database Size and Space Available

[JohnBates]

hi everyone,

This has been a recurring issue with out SQL Server 2000 db.

Even though I am dropping/deleting large tables (some as large as 4 GB), that space is not being made available as free space on the c drive. The db keeps it... the dbproperties show that the space available is increasing when i drop a table. The databases space avail is now 13 GB ! I need that space back but SQL Server is holding it for some reason. Even after shrinking the db, it still holds it.

When I try to change the size for data to 22,000 MB, it says
"you cannot chg size to less than current size"... or something to that effect.

Here are the specs for the db:

SS2000 sp3a
was created with an initial size of 2GB
Auto grow for both Data and Log
Grow by 200 MB increments
Recovery model: Simple
DB size: 32 GB Data 32 GB Tlog 1MB Space avail 13 GB

How can I get this space back for other use?
Thanks, John

 

[nigelrivett]

Dropping a table won't affect the size of the files - but a shrink should.
If this is a user database then try shrinking the tr log.
see

http://www.nigelrivett.net/TransactionLogFileGrows_1.html

You have a simple recovery model so it should work ok.

You cannot shrink a file to a smaller size than it was created or expanded to via an lter database command but if you have just let it expand then you should be able to shrink it.

======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

 

[JohnBates]

thanks for the reply nigelrivett,

It's the data file that is out of control.The log file is now 1mb (after shrinking the db)

Presently, I'm running a Shrink File task for the data file, specifying 20,000 MB as the new size .

There is heavy disk activity on the server for the last 45 mins - hopefully this will work

John