Remove unused space

[JanS]

Hi,

My apologies if a similar question has been dealt with in the past, the search engine of this website is down for maintenance.

I am wondering if someone can help me determine how all the seemingly unused space can be freed from a 10GB SQL 2000 SP4 DB.

Current results of sp_spaceused are:
reserved data index_size unused
------- ----------- ------------------ ------------------
10339624 KB 3172728 KB 1100400 KB 6066496 KB

I've defragged and rebuilt indexes, updated stats etc and still cant find how to remove the 6GB of free space.

I have retrieved the size of all tables/views/indexes and determined that the numbers shown seem to be correct. The actual size of the DB does appear to be around 4 GB.

Also, Enterprise Manager / Taskpad shows about 400MB free.

Can anyone help me locate and remove the "missing" 6GB? Or explain the discrepancy with Enterprise Manager?

TIA
Jan

 

[mrdenny]

You can use the DBCC SHRINKFILE command to free up the space in the database files. Look up DBCC SHRINKFILE in Books OnLine for the full syntax.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[JanS]

I had tried this, file size still above 10GB. Its a mystery whats using all the space. One Id like to solve as the size of backup files is becoming unmanageable.

Cheers
Jan

 

[onpnt]

DBCC SHRINKDATABASE

Watch your growth. If it's set to grow some big size you may just be on the edge of that growth being executed and that may be why the size if so much

what do you get when you run

exec sp_helpdb [database name]


[sub]____________ signature below ______________
I am Tedward Keyboardhands!!!
You are a amateur developer until you realize all your code sucks.
Jeff Atwood[/sub]

 

[mrdenny]

If your backup files are about the same size, then you actually have that much data. Backup files to not include white space in them.

Check the transaction log, if you aren't backing that up and the database is in full recovery mode you will be backing that up.

If you've just done a bunch of database maintenance you may need to wait for a few hours before the SQL Server will allow you to move the data pages around.

Also do a:

sp_spaceused @updateusage'updateusage'

This will update the metadata in case the size info isn't correct.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com