Mr or Mrs. 500
Group: General Forum Members
Last Login: Friday, July 22, 2016 8:58 AM
I executed the following code to determine the size that a certain table takes no the database:
o.name AS Table_Name,
i.index_id, i.type as i_type, p.rows,
a.total_pages, a.used_pages, a.data_pages, a.type alloc_type
from sys.objects o
inner join sys.indexes i on o.object_id = i.object_id
inner join sys.partitions as p on i.object_id = p.object_id and i.index_id = p.index_id
inner join sys.allocation_units as a on p.partition_id = a.container_id
where o.is_ms_shipped = 0 and o.name= 'AlertasUtilizadorLog'
order by o.name, i.type
exec sp_spaceused 'AlertasUtilizadorLog'
I also executed the sp_spaceused to compare both results.
The output was:
Table_Name index_id i_type rows total_pages used_pages data_pages alloc_type
AlertasUtilizadorLog 0 0 319 57 54 53 1
AlertasUtilizadorLog 0 0 319 209 208 0 2
AlertasUtilizadorLog 2 2 319 4 4 2 1
name rows reserved data index_size unused
AlertasUtilizadorLog 319 2160 KB 2088 KB 40 KB 32 KB
the reserved size is the sum of total_pages.
the data size is the sum of data_pages for alloc_type = 1 and used_pages for alloc_type = 2 and index_type <= 1 (CLUSTERED OR HEAP).
the unused size is the sum of total_pages less the sum of used_pages.
now comes my "problem"... the sum of used_pages for indexes (i_type > 1) is 4 and sp_spaceused says 5...
How is that?! I can get that value, just subtract used_pages to total_pages and I get that value but why hasn't the sum of all indexes have that value?!
Just one more... total_pages or reserved is the size the data occupies on disk... but used_pages isn't suppose to be the data space or does it have "trash" and the real data is data_pages?
If you need to work better, try working less...