Its a beautiful Sunday morning. l have a lot of things on my mind to write on the blog but I’ve been very busy lately and it really bothers me sometimes not been able to contribute more important stuff that could help someone out there. Ok, so l’m writing on this stored procedure called sp_spaceused. I actually started using this not that long ago when I faced space issues and l had to track how data was flowing into some databases at work. When data becomes really huge and storage problems become prominent, this procedure can be used to be tracking how fast the storage is being used and you can make decisions such as adding additional storage or implementing some archiving strategies to your systems. So, sp_spaceused gives you information about the size of the database, space used by data and indexes and also space that is reserved but unused and finally unallocated space. Below is the screen shot of the procedure executed against the famous adventureworks database.
executing sp_spaceused returned the above result set. It is good to note that the procedure can be executed in any database without specifying any object and it will give information about the database. On the other hand, if you are interested in getting the size information about a particular object such as a table, then that table can be provide and the procedure will provide size information on that table. For example if you want to find information on the employee table in adventureworks, you execute
sp_spaceused ‘HumanResources.Employee’ and the result is shown below.
So what is the difference between this screen shot and the one for the database itself. Well, its obvious but let’s get into detail about what all these columns mean in the result set. Starting with the database itself, we see the database_name and database_size which needs no explanation. There is unallocated_space, what does that mean? This particular column can easily mislead lot of people. It refers to a space in the database that can not be occupied by any object. Its like useless space unless you fix it. This spaces results from different activities that leave behind a bubble of space that is not available for objects. There are many ways to remove it such has shrinking database files but this has to be done with caution. The next column is reserved, which is actually the total space reserved for objects in the database. The reserved space is the only space available to create objects. The column, data, identifies the space occupied by data in the database. Index_size specifies the size of all indexes in the database and the unused column identifies the space left in the reserved space after the current storage taken be existing objects and data in the database.
The result set when sp_spaceused is executed for the employee table has similar columns to the ones discussed above. The row column simply tells how many rows of record are available in the table. You might be wondering why sp_spaceused executed without an object produces multiple result sets but when you provide an object, you get only one result set. This is the other part of the story l want to discuss because when you have multiple result sets, its very difficult to retrieve those columns and information and store it in a table. For example, for reporting or storing it for reference. Well, it is possible to edit the original stored procedure and produce your version which you can use for your work. Lets get into that and see how we can accomplish it.
First of all, All system stored procedures are located in programmability->stored procedures->system stored procedures
I am not going to copy and past the entire original procedure here but below is a modified version that you can use to get a single result set for sp_spaceused when you ran it without specifying an object, such as getting information on a database. To use the script, you simply execute it in your database and produce your own stored procedure which you can call just in the same way you call your procedures.
Create procedure smart_spaceused
@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 < 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
select
database_name = db_name(),
database_size = convert (dec (15,0),((convert (dec (15,2),@dbsize) + convert (dec (15,2),@logsize))
* 8192 / 1024)),
‘unallocated space’ = convert (dec (15,0),(case when @dbsize >= @reservedpages then
(convert (dec (15,2),@dbsize) – convert (dec (15,2),@reservedpages))
* 8192 / 1024 else 0 end))
,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 < 2) THEN row_count
ELSE 0
END
)
FROM sys.dm_db_partition_stats
WHERE object_id = @id;
/*
** Check if table has XML Indexes or Fulltext Indexes which use internal tables tied to this table
*/
IF (SELECT count(*) FROM sys.internal_tables WHERE parent_id = @id AND internal_type IN (202,204,211,212,213,214,215,216)) > 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
return (0) — sp_spaceused
GO
Here is the output when l run the edited version.
We can see the edited version gives single result set for both the table and the database and it makes life easy storing this information into a table or for reporting purposes.
I hope this information is useful and will help someone to quickly get the space information about their databases and tables and actually be able to store those information without struggle. Comments, questions and feedback welcome.
