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

StackExchange Database Administrators Q#177424, answer score: 2