-
-
Space used by the objects in the database is a tricky subject, this can be very useful when planning your database maintenance plan
a system stored procedure as you probably had figured is called sp_spaceused gets you the needed information
that procedure can be used in multiple ways
use Northwind
exec sp_spaceused
when this is executed against your database this data appears
it shows two result sets the first one which is a Whole Database statistics
- DatabasName
- database size in MBs and
- Space in the database that has not been reserved for database objects in MB also (this is space that is reserved by the Database and has not been allocated to any database objects yet)
the second group shows
- data that has been reserved for all objects (this includes pages that has no data and fragmented pages)
- Actual Data size inside the tables pages
- index size
- unused space
sometimes you might get negative values, that is normal as sometimes the usage information is not updated in that case you can do this
USE Northwind
GO
EXEC sp_spaceused @updateusage = N'TRUE';
it will update usage information and then return the same values as the first sample
now. you can also get statistics that are specific to an object
you can do this simply by
USE Northwind;
GO
EXEC sp_spaceused N'Employees';
GO
in addition to reserved space and data and index size you also get the rows count