I'm using SQL Server Express 2008 R2 and a third part application has its database on this database server. The database seems way bigger than I would expect considering what it is actually doing. When I do a rough add up of the space used by the tables and indexes I get to about half of the total size of the database file.
I am wondering what is using up the rest of the space. I understand SQL Server Express is limited to 10GB which is why I'm concerned as the database is about 2/3 full. I also understand that there is free space within the database itself which is no problem. I'm not short of disk space.
Here is how I have found the size of the tables and indexes. I don't understand these queries very well as I googled them but they seem to report the numbers I'm interested in:
-- Size of database and how much space is used SELECT (SELECT CONVERT(DECIMAL(18,2), SUM(CAST(df.size as float))*8/1024.0) FROM sys.database_files AS df WHERE df.type in ( 0, 2, 4 ) ) AS [DbSize], CONVERT(DECIMAL(18,2), SUM(a.total_pages)*8/1024.0) AS [SpaceUsed], (SELECT CONVERT(DECIMAL(18,2), SUM(CAST(df.size as float))*8/1024.0) FROM sys.database_files AS df WHERE df.type in (1, 3)) AS [LogSize] FROM sys.partitions p join sys.allocation_units a on p.partition_id = a.container_id;
DbSize SpaceUsed LogSize ------- --------- ------- 7829.25 6130.09 611.13
OK so what is using that 6.1 GB?
The following query lists the sizes of all the tables and indexes (source):
;with cte as ( SELECT t.name as TableName, SUM (s.used_page_count) as used_pages_count, SUM (CASE WHEN (i.index_id < 2) THEN (in_row_data_page_count + lob_used_page_count + row_overflow_used_page_count) ELSE lob_used_page_count + row_overflow_used_page_count END) as pages FROM sys.dm_db_partition_stats AS s JOIN sys.tables AS t ON s.object_id = t.object_id JOIN sys.indexes AS i ON i.[object_id] = t.[object_id] AND s.index_id = i.index_id GROUP BY t.name ) select cte.TableName, cast((cte.pages * 8.)/1024 as decimal(10,3)) as TableSizeInMB, cast(((CASE WHEN cte.used_pages_count > cte.pages THEN cte.used_pages_count - cte.pages ELSE 0 END) * 8./1024) as decimal(10,3)) as IndexSizeInMB from cte order by 2 desc
If I add up all the tables and indexes with a size over 1mb, it comes to under 3GB in total. (I tried sum in the above query but couldn't figure that out)
If I post the full output here it is very long and messy.
So it seems about half of the expected free space is being used up by something else.
Any ideas what I'm missing here or, do I need a better way to add up used space?