What tools are available for providing a breakdown of the diskspace used by an SQL Server database

Question

I have an MSDE2000 database which appears to be approaching it's 2Gb limit.

What tools can I use to determine where all the space is being used?

Ideally think TreesizePro for SQL Databases

Answer 1

Whilst the DB size may be, say, 1.5GB, it may only be containing 500MB of data. This will depend on many factors (i.e. auto-growth size, index fill factors and so on). Run sp_spaceused to find out how much is unallocated. You should then be able to use the likes of DBCC SHRINKDB to reclaim some space.

To just see the size of the file you could just look on the disk. There will be a data file (.MDF) and a log file (.LDF) - unless you have split the DB across multiple file-groups which I don't know if you can do in MSDE.

If you want to find out what tables/indexes use most space (and assuming you don't have Enterprise Manager to simply look at the Taskpad View - which would also give you the info from above), then you can execute sp_spaceused with a tablename as a parameter. It wouldn't take long to run against all, or to write a script to loop through all the tables.