Sql-server – MS SQL Server 2008 makes hard disk space disappear

We are running SharePoint 2010 with MS SQL Server 2008 (Windows Server 2008 R2 Enterprise). We have not taken regular SP backups this far which has led to high disk usage. However, even if I shrink databases, we still have a strange disk usage issue. If I take a listing of the all database disk usages, their total disk usage is 21 GB (plus logs taking 9 GB). Still, the allocated hard disk space is 145 GB. If I look at the files in the hard disk using Windows Explorer, I only find a few little files even when hidden files are shown.

The hard drives of the machine are:

• C (System drive); 32 GB allocated of 40 GB
• D (SQL Server); 8 GB allocated of 15 GB
• E (SQL-DB); 145 GB allocated of 180 GB
• F (SQL-Logs); 8 GB allocated of 30 GB

If I shrink database logs, it seems that F drive receives free space. Shrinking other databases does not seem to release space on E – even if it did, E still uses 145 GB (versus 21 GB of database sizes in total). Naturally, SQL Server might not release disk space without explicitly telling it to do it. Still, the database size has never been even close to 145 GB but at most some 30 GB.

Where does the space go?

Below is the script I use to retrieve database sizes in MS SQL Server Management Studio. It seems to list all the databases as I compare it to the databases treeview.

with fs
as
(
    select database_id, type, size * 8.0 / 1024 size
    from sys.master_files
)
select 
    name,
    (select sum(size) from fs where type = 0 and fs.database_id = db.database_id) DataFileSizeMB,
    (select sum(size) from fs where type = 1 and fs.database_id = db.database_id) LogFileSizeMB
from sys.databases db