patternsqlMinor
Does sp_spaceused data contain more than the sum of each table data?
Viewed 0 times
theeachsp_spaceusedcontainthanmoredoessumdatatable
Problem
I wanna monitor the size of each table in my database. After doing so, I was wondering why the sum of the data of each table does not correnspond to the data size of the whole database I get after calling sp_spaceused.
Here are my results:
ExampleDatabase: 46 589 544 KB (data)
Sum(data) of each table: 18 564 680 KB (data)
The only objects I was ignoring while saving each tables size (data) were the system-tables and some tables, whose size is not worth talking about.
Because I can't see the error in my "catch table size procedure", I'm wondering if it's the sp_spaceused.data column, which maybe contains more than the sum of each table data?
Here are my results:
ExampleDatabase: 46 589 544 KB (data)
Sum(data) of each table: 18 564 680 KB (data)
The only objects I was ignoring while saving each tables size (data) were the system-tables and some tables, whose size is not worth talking about.
Because I can't see the error in my "catch table size procedure", I'm wondering if it's the sp_spaceused.data column, which maybe contains more than the sum of each table data?
Solution
What I understand is that the sum of the tables will not be equals the database size. On msdn it says:
database_size will always be larger than the sum of reserved +
unallocated space because it includes the size of log files, but
reserved and unallocated_space consider only data pages.
Pages that are used by XML indexes and full-text indexes are included
in index_size for both result sets. When objname is specified, the
pages for the XML indexes and full-text indexes for the object are
also counted in the total reserved and index_size results.
If space usage is calculated for a database or an object that has a
spatial index, the space-size columns, such as database_size,
reserved, and index_size, include the size of the spatial index.
Reference here
database_size will always be larger than the sum of reserved +
unallocated space because it includes the size of log files, but
reserved and unallocated_space consider only data pages.
Pages that are used by XML indexes and full-text indexes are included
in index_size for both result sets. When objname is specified, the
pages for the XML indexes and full-text indexes for the object are
also counted in the total reserved and index_size results.
If space usage is calculated for a database or an object that has a
spatial index, the space-size columns, such as database_size,
reserved, and index_size, include the size of the spatial index.
Reference here
Context
StackExchange Database Administrators Q#13635, answer score: 2
Revisions (0)
No revisions yet.