Hello,

I have database with many tables.  The backup file size of the database is approximately 27GB.  However, when I add up all the sizes of all the tables (data + index), it is only a bit over 6GB.  So, what is taking the rest of 21GB?

Below is the query I use for calculating the sizes of tables and indexes:

    SELECT Name, Rows, CONVERT(NUMERIC(10,2),CONVERT(INT,REPLACE(Data,'KB',''))/1024.0) AS 'Data_SpaceUsed(MB)', CONVERT(NUMERIC(10,2),CONVERT(INT,REPLACE(Index_Size,'KB',''))/1024.0) AS 'Index_SpaceUsed(MB)'
    FROM #SpaceUsed

Does anyone have any idea what the rest of the space is being used by?


Thank you!

 How big is your transaction log?

 It is 2.5GB

Did you run DBCC UPDATEUSAGE to recalculate the values?

Yes, I always execute DBCC UPDATEUSAGE(0) WITH NO_INFOMSGS before getting the space used information. 

 Any other ideas?

Thanks! 

Guys,

I digged deeper and found out that there is 1 table which is causing this confusion.  When I run EXEC sp_spaceused 'Audit', I get:

Name: Audit
Rows: 12228956
Reserved: 24440904 KB
Data: 3768904 KB
Index_Size: 56 KB
Unused: 20671944 KB

So, according to this result, most of this table's space is unused.

But when I look at the available space to shink the database file, by running the following command provided by microsoft on the BOL:

SELECT name , size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0 AS AvailableSpaceInMB
FROM sysfiles, I get the following result:

Name        AvailableSpaceMB
GN_Data        49.43
GN_Log        2610

This appears to imply that I cannot shrink the data file.

My question is: How can I get rid of the unused space in the "Audit" Table?


Thanks a lot!   

 

It looks like the solution is to either:

1. Add a clustered index (since I don't have it)
2. Rebuild this index

or

1. Copy this table to another temp table, drop the original, and then rename temp to original name

In either case, I should SHRINK the database as space should be freed up after this operation

In both cases, the transaction log will most likely grow, so this should be monitored.