Database or Object Size in SQL server [sp_spaceused]

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

1. DatabasName
2. database size in MBs and
3. 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

1. data that has been reserved for all objects (this includes pages that has no data and fragmented pages)
2. Actual Data size inside the tables pages
3. index size
4. 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