Using sp_spaceused

sp_spaceused is a system stored procedure that reveals the amount of space used. This procedure can take many different parameters as input in order to show the amount of space on disk that is used. If your system can afford it, it is best to update the usage stats before finding the space used. This ensures accurate results from sp_spaceused.

The most common usage for sp_spaceused is to determine the amount of spaced used for a database. In order to perform this, execute the procedure with no parameters

Database Size

[cc lang=”sql”]
— Find space used for current database
EXEC sp_spaceused
[/cc]
Below we see that the results for sp_spaceused returns two result sets. The first result set shows a high level view of the database space. With the most important being database size, showing the amount of disk space used for both the data and log files.

The second result set shows the allocation within the database and does not include the log file usage. The total amount is shown under “reserved”. And the Data, Index, and unused spaces are split up to give a good breakdown of what is utilizing the most space.

sp_spaceused_database_results

sp_spaceused database results

Table Size

In order to run this command for a table pass in the table name with schema name enclosed in single quotes.
[cc lang=”sql”]
— Find space used for specific table
EXEC sp_spaceused ‘SalesLT.Address’
[/cc]
Evaluating this output looks similar to the second record set returned by the database results, except we also get the number of rows contained in the table. This number of rows may not be precisely correct because it gets the number by looking at the table statistics instead of doing an actual count of the rows.

sp_spaceused table results

sp_spaceused table results

4 comments
David 16 Sep 2011 at 11:45 am

Hi There, I figured out…
by creating my own procedure:
(I used the sp_usedspace as template)

/****** Object: StoredProcedure [dbo].[proc_spaceused] Script Date: 09/16/2011 11:10:27 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
— =============================================
— Author: David
— Create date:
— Description: replace sys.sp_spaceused with something more useful
— =============================================
ALTER PROCEDURE proc_spaceused
— Add the parameters for the stored procedure here
@objname nvarchar(776) = null, — The object we want size on.
@updateusage varchar(5) = false — Param. for specifying that
— usage info. should be updated.
as

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

/*
** Check to see if user wants usages updated.
*/

if @updateusage is not null
begin
select @updateusage=lower(@updateusage)

if @updateusage not in (‘true’,’false’)
begin
raiserror(15143,-1,-1,@updateusage)
return(1)
end
end
/*
** Check to see that the objname is local.
*/
if @objname IS NOT NULL
begin

select @dbname = parsename(@objname, 3)

if @dbname is not null and @dbname db_name()
begin
raiserror(15250,-1,-1)
return (1)
end

if @dbname is null
select @dbname = db_name()

/*
** Try to find the object.
*/
SELECT @id = object_id, @type = type FROM sys.objects WHERE object_id = object_id(@objname)

— Translate @id to internal-table for queue
IF @type = ‘SQ’
SELECT @id = object_id FROM sys.internal_tables WHERE parent_id = @id and internal_type = 201 –ITT_ServiceQueue

/*
** Does the object exist?
*/
if @id is null
begin
raiserror(15009,-1,-1,@objname,@dbname)
return (1)
end

— Is it a table, view or queue?
IF @type NOT IN (‘U ‘,’S ‘,’V ‘,’SQ’,’IT’)
begin
raiserror(15234,-1,-1)
return (1)
end
end

/*
** Update usages if user specified to do so.
*/

if @updateusage = ‘true’
begin
if @objname is null
dbcc updateusage(0) with no_infomsgs
else
dbcc updateusage(0,@objname) with no_infomsgs
print ‘ ‘
end

set nocount on

/*
** If @id is null, then we want summary data.
*/
if @id is null
begin
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 = @reservedpages then
(convert (dec (15,2),@dbsize) – convert (dec (15,2),@reservedpages))
* 8192 / 1024 else 0 end))

/*
** Now calculate the summary data.
** reserved: sum(reserved) where indid in (0, 1, 255)
** data: sum(data_pages) + sum(text_used)
** index: sum(used) where indid in (0, 1, 255) – data
** unused: sum(reserved) – sum(used) where indid in (0, 1, 255)
*/
,–select
reserved = convert (dec (15,0),(@reservedpages * 8192 / 1024)),
data = convert (dec (15,0),(@pages * 8192 / 1024) ),
index_size = convert (dec (15,0),((@usedpages – @pages) * 8192 / 1024.)),
unused = convert (dec (15,0),((@reservedpages – @usedpages) * 8192 / 1024)),
unit = ‘KB’
end

/*
** We want a particular object.
*/
else
begin
/*
** Now calculate the summary data.
* Note that LOB Data and Row-overflow Data are counted as Data Pages.
*/
SELECT
@reservedpages = SUM (reserved_page_count),
@usedpages = SUM (used_page_count),
@pages = SUM (
CASE
WHEN (index_id < 2) THEN (in_row_data_page_count + lob_used_page_count + row_overflow_used_page_count)
ELSE lob_used_page_count + row_overflow_used_page_count
END
),
@rowCount = SUM (
CASE
WHEN (index_id 0
BEGIN
/*
** Now calculate the summary data. Row counts in these internal tables don’t
** contribute towards row count of original table.
*/
SELECT
@reservedpages = @reservedpages + sum(reserved_page_count),
@usedpages = @usedpages + sum(used_page_count)
FROM sys.dm_db_partition_stats p, sys.internal_tables it
WHERE it.parent_id = @id AND it.internal_type IN (202,204,211,212,213,214,215,216) AND p.object_id = it.object_id;
END

SELECT
name = OBJECT_NAME (@id),
rows = convert (char(11), @rowCount),
reserved = convert (dec (15,0), (@reservedpages * 8) ),
data =convert (dec (15,0),(@pages * 8)),
index_size = convert (dec (15,0),((CASE WHEN @usedpages > @pages THEN (@usedpages – @pages) ELSE 0 END) * 8)),
unused = convert (dec (15,0), ((CASE WHEN @reservedpages > @usedpages THEN (@reservedpages – @usedpages) ELSE 0 END) * 8) ),
unit = ‘KB’

end
Print ‘Microsoft sucks’

return (0) — sp_spaceused

David 16 Sep 2011 at 11:47 am

replace the 8) with 8 )

Derek Dieter 16 Sep 2011 at 12:18 pm

Hi David,

Thank you for Sharing this.

Derek

David 14 Sep 2011 at 3:49 pm

Hi there,
when I try to fill the results into a temporary table, it gives an error:
Column name or number of supplied values does not match table definition.

my code:
DECLARE @tempTable TABLE (
database_name nvarchar(128),
database_size varchar(18),
[unallocated space] varchar(18),
reserved varchar(18),
data varchar(18),
index_size varchar(18),
unused varchar(18)
)

INSERT @tempTable
exec SP_SPACEUSED

how can I catch the results from sp_spaceused

Featured Articles

 Site Author

  • Thanks for visiting!
css.php