MDF file is close to 900gb but my calculation of reserved table space is less than 30gb... what am I missing? In a nutshell, my server is running out of free hard drive space so I ran the script found here to get an idea of tables I could possibly delete old data from:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=10932
but when I got the results it seems that deleting old data doesn't seem to be my solution. I have a feeling there is something else I should be aware of here...

results from sp_spaceused

database_name   database_size   unallocated space
prod            993333.19 MB    94327.32 MB


reserved        data         index_size unused
834819768 KB    833565632 KB 1065480 KB 188656 KB


The results of my "find big tables" script:

reserved_KB     data_KB     index_size_KB   unused_KB
27,257,368      26,126,568  1,003,184       127,616
Since it sounds like you purged data from you tables, the next step is to release the free space within the datafile (.mdf) back to the OS.  You can do this via a DBCC SHRINKFILE statement.

If this doesn't result in releasing sufficient disk back to the OS, you should rebuild indexes on those tables you performed heavy purge operations against in case they are heap tables and then re-issue the DBCC SHRINKFILE command.

I would suggest leaving 20 - 30% free space in the mdf, which if I'm reading the question right, should be 36GB - 40GB in your case. This will avoid growth operations from occurring in the near future.

Finally, after the SHRINKFILE operation completes, you'll want to REBUILD any heavily fragmented indexes as the SHRINKFILE operation will cause fragmentation for any indexes that were stored toward the tail end of the data file.

EDIT: So it seems your calculation of reserved table space (as referenced in the question header) is off by a significant margin based on the comments on this answer.  In this case, what I would suggest you do is run the following statement to get an idea of what's using your disk space:

DECLARE @String NVARCHAR (4000), @ExecString NVARCHAR ( 4000)

DECLARE dbCursor CURSOR
FOR
        SELECT name
        FROM sys .databases
        WHERE name LIKE '%'

CREATE TABLE ##tmpAllDBs
(
     DBID               INT,
     DatabaseName     SYSNAME,
     Name               SYSNAME,
     FileName          SYSNAME,
     Size               INT,
     UsedSpace          INT,
     ID                    INT,
     FileGroup          SYSNAME
)

SET @String = '
USE [?]

DECLARE     @PageSize     FLOAT
SELECT     @PageSize = v.low/1024.0
FROM     [master].[dbo].[spt_values] v
WHERE     v.number=1
          AND v.type=''E''

CREATE TABLE #tmpspc
(     Fileid               INT,
     FileGroup          INT,
     TotalExtents     INT,
     UsedExtents          INT,
     Name               SYSNAME,
     FileName          NCHAR(520)
)
INSERT #tmpspc
EXEC (''DBCC ShowFileStats'')

CREATE TABLE #tmplogspc
(     DatabaseName     SYSNAME,
     LogSize               FLOAT,
     SpaceUsedPerc     FLOAT,
     Status               BIT
)
INSERT #tmplogspc
EXEC (''DBCC SQLPerf(LogSpace)'')

INSERT INTO ##tmpAllDBs
SELECT     db_id() AS dbID,
          db_name() AS [DatabaseName],
          RTRIM(s.name) AS [Name],
          RTRIM(s.filename) AS [FileName],
          (s.size * @PageSize) AS [Size],
          CAST(tspc.UsedExtents * CONVERT(FLOAT,64) AS FLOAT) AS [UsedSpace],
          CAST(s.fileid AS INT) AS [ID],
          RTRIM(g.groupname) AS [FileGroup]
FROM     [dbo].[sysfilegroups] AS g
          INNER JOIN [dbo].[sysfiles] AS s
               ON s.groupid=CAST(g.groupid AS INT)
          INNER JOIN #tmpspc tspc
               ON tspc.Fileid = CAST(s.fileid AS INT)

UNION

SELECT     db_id() AS dbID,
          db_name() AS [DatabaseName],
          RTRIM(s.name) AS [Name],
          RTRIM(s.filename) AS [FileName],
          (s.size * @PageSize) AS [Size],
          (tspclog.LogSize * tspclog.SpaceUsedPerc * 10.24)
               / ( SELECT     COUNT(s_sub.name)
                    FROM     [dbo].[sysfiles] s_sub
                    WHERE     s_sub.groupid = 0)
          AS [UsedSpace],
          CAST(s.fileid AS INT) AS [ID],
          ''LOG FILE'' AS FileGroup
FROM     [dbo].[sysfiles] AS s
          INNER JOIN #tmplogspc tspclog
               ON tspclog.DatabaseName = db_name()
WHERE     (s.groupid = 0)

DROP TABLE #tmpspc
DROP TABLE #tmplogspc
'
--EXEC sp_MSforeachdb @String

DECLARE @name NVARCHAR (128)
OPEN dbCursor
FETCH NEXT FROM dbCursor
INTO @name

WHILE @@FETCH_STATUS = 0
BEGIN

     SELECT @ExecString = REPLACE( @String, '?', @name )
        EXECUTE (@ExecString )

        FETCH NEXT FROM dbCursor
        INTO @name
END

-- Close and deallocate the cursor because you've finished traversing all it's data
CLOSE dbCursor
DEALLOCATE dbCursor

SELECT DBID , DatabaseName , Name, FileName, Size /1024.0 AS SizeMB, UsedSpace /1024.0 AS UsedSpaceMB,
     (1.0 * UsedSpace)/(1.0 * Size) AS PercentUsed , ID, FileGroup
FROM ##tmpAllDBs
ORDER BY 2, 8

DROP TABLE ##tmpAllDBs


This returns results that show how much size a file is using according to the OS (SizeMB), how much data is actually contained within the file (UsedSpaceMB) and some other information about the data files.  The script is ugly, but it works all the way back to SQL 2000.

With this output in had, what I would do is look for any files on User Databases where the PercentUsed column is low and the database is not tempdb (I'll talk about what to do with tempdb further below), and then run DBCC SHRINKFILE operations against said files.  This will give back the OS some space, but by no means does this mean your job is done.

Once this is complete, the next step is to identify what data you can purge or which objects you can drop.  Purge old data, drop unused indexes, and then rerun the script and see what else can be reduced in size.  Again, rebuilding indexes is recommended, but as you've already seen, Index Rebuild operations may cause data files to grow so you may have to weigh if a defragmented index is worth the extra consumption of disk space.

Other options available to you will depend on your SQL Server Version and Edition.  If you're running Enterprise or SQL 2016 Standard SP1 or later, you can take advantage of Table Compression.  This can help reduce the space used within your data files as well.

If you see that tempdb is consuming a large amount of space which is relatively unused, reduce the the size of the tempdb files, but make sure that if you have multiple .mdf files, you shrink them all to the same size as per MS's recommended guidelines.  Another word about tempdb, shrinking it is often an act of futility because there's often activity occurring within the database, so you may have to restart the instance to get these files to shrink down as desired.  Here's MS's documentation on the various ways to shrink tempdb.

Another possibility is that you have non-database related clutter on your drive(s).  In this case, simply deleting these files may be the fix for your issue.  I find using WinDirStat helps quickly identify where some of the larger files are.  Be sure you know what you're deleting though.  Sometimes there are lots of "seemingly worthless" files sitting in hidden directories, such as the WinSxS folder that look like they could be quickly deleted.  Don't recklessly delete files; make sure you only purge files you know are safe to remove.

Finally, ask for money and buy some additional storage.  Your server just may be at capacity and it's time for an upgrade.  Hardware isn't always the answer, but sometimes it can be the best answer.