You are currently browsing comments. If you would like to return to the full story, you can read the full entry here: “Using sp_spaceused”.
Random MVP RSS Feeds- Could not connect to Twitter
Connect
You are currently browsing comments. If you would like to return to the full story, you can read the full entry here: “Using sp_spaceused”.
Random MVP RSS Feeds
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 *
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) *
unit = ‘KB’
end
Print ‘Microsoft sucks’
return (0) — sp_spaceused
replace the
with 8 )
Hi David,
Thank you for Sharing this.
Derek
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