sql Calculate Space Used

declare @id	int--The object id that takes up space
		,@type	character(2)--The object type.
		,@pages	bigint--Working variable for size calc.
		,@dbname sysname
		,@dbsize bigint
		,@logsize bigint
		,@reservedpages  bigint
		,@usedpages  bigint
		,@rowCount bigint

select
	@dbsize = sum(convert(bigint,case when status & 64 = 0 then size else 0 end))
	,@logsize = sum(convert(bigint,case when status & 64 <> 0 then size else 0 end))
from dbo.sysfiles

select
	@reservedpages = sum(a.total_pages),
	@usedpages = sum(a.used_pages),
	@pages = sum(
			CASE
				-- XML-Index and FT-Index internal tables are not considered "data", but is part of "index_size"
				When it.internal_type IN (202,204,211,212,213,214,215,216) Then 0
				When a.type <> 1 Then a.used_pages
				When p.index_id < 2 Then a.data_pages
				Else 0
			END
		)
from sys.partitions p
join sys.allocation_units a on p.partition_id = a.container_id
left join sys.internal_tables it on p.object_id = it.object_id

/* unallocated space could not be negative */
select
	--Name of the current database.
	database_name = db_name(),
	
	--Size of the current database in megabytes. database_size includes both data and log files.
	database_size = ltrim(str((convert (dec (15,2),@dbsize) + convert (dec (15,2),@logsize)) 
		* 8192 / 1048576,15,2)),--mb
		
	--Space in the database that has not been reserved for database objects.
	'unallocated_space' = ltrim(str((case when @dbsize >= @reservedpages then
		(convert (dec (15,2),@dbsize) - convert (dec (15,2),@reservedpages)) 
		* 8192 / 1048576 else 0 end),15,2)),--mb

	--Total amount of space allocated by objects in the database.
	reserved = ltrim(str(@reservedpages * 8192 / 1024. / 1024.,15,0)),

	--Total amount of space used by data.
	data = ltrim(str(@pages * 8192 / 1024. / 1024.,15,0)),
	
	--Total amount of space used by indexes.
	index_size = ltrim(str((@usedpages - @pages) * 8192 / 1024. / 1024.,15,0)),
	
	--Total amount of space reserved for objects in the database, but not yet used.
	unused = ltrim(str((@reservedpages - @usedpages) * 8192 / 1024. / 1024.,15,0))
Variation of sp_spaceused, in that instead of returning 2 recordsets, it returns just the one

Updated: Tuesday 14th June 2011, 06:45pm

There are 0 comments

Leave a comment of your own

Comments are currently closed.