December 13, 2006 at 6:02 am
Hi all
I guess sp_spaceused is the most widely used way to get the space allocated to the database. I have just found some interesting information about this stored procedure. Execute the following queries and check out the results....
In 2000: select sum(reserved) from master.dbo.sysindexes where indid in(0,1) or indid>250;
The resultset * 8(default page size) is the size displayed in the sp_spaceused.
In 2005: The above query should ideally translate into something like this
select sum(reserved_page_count) from master.sys.dm_db_partition_stats where index_id in(0,1) or index_id>250;
check this resultset * 8 against the sp_spaceused...they are not the same
but select sum(reserved_page_count) from master.sys.dm_db_partition_stats;
returns the correct result... which means the 2005 implementation of the sp_spaceused has also included the nonclustered indexes.....
In my test setup I found this and I am sure that the implementation of SP_SPACEUSED is different in 2000 and 2005. please try this out and post your comments....
Thanks
Vijay
December 13, 2006 at 7:02 am
THe following is the implementation of sp_spaceused in 2005. You can compare it with that of SQL 2000.
create procedure sys.sp_spaceused --- 2003/05/19 14:00
@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-Docid is not considered "data", but is part of "index_size"
When it.internal_type IN (202,204) 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
database_name = db_name(),
database_size = ltrim(str((convert (dec (15,2),@dbsize) + convert (dec (15,2),@logsize))
* 8192 / 1048576,15,2) + ' MB'),
'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')
/*
** 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 = ltrim(str(@reservedpages * 8192 / 1024.,15,0) + ' KB'),
data = ltrim(str(@pages * 8192 / 1024.,15,0) + ' KB'),
index_size = ltrim(str((@usedpages - @pages) * 8192 / 1024.,15,0) + ' KB'),
unused = ltrim(str((@reservedpages - @usedpages) * 8192 / 1024.,15,0) + ' KB')
end
/*
** We want a particular object.
*/
else
begin
/*
** Now calculate the summary data.
** Regarding rowcount: We want to add row count from all partitions, but
** note that a row in sys.partitions may join with multiple rows in
** sys.allocation_units as it may have data, SLOB and LOb allocation units.
*/
select @reservedpages = sum(a.total_pages),
@usedpages = sum(a.used_pages),
@pages = sum(
CASE
When a.type <> 1 Then a.used_pages
When p.index_id < 2 Then a.data_pages
Else 0
END
),
@rowCount = sum(
CASE
When (p.index_id < 2) and (a.type = 1) Then p.rows
Else 0
END
)
from sys.partitions p, sys.allocation_units a
where p.partition_id = a.container_id and p.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)) > 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(a.total_pages),
@usedpages = @usedpages + sum(a.used_pages)
from sys.partitions p, sys.allocation_units a, sys.internal_tables it
where p.partition_id = a.container_id and p.object_id = it.object_id and it.internal_type IN (202,204) and it.parent_id = @id;
end
select name = object_name(object_id(@objname)),
rows = convert(char(11), @rowCount),
reserved = ltrim(str(@reservedpages * 8192 / 1024.,15,0) + ' KB'),
data = ltrim(str(@pages * 8192 / 1024.,15,0) + ' KB'),
index_size = ltrim(str((@usedpages - @pages) * 8192 / 1024.,15,0) + ' KB'),
unused = ltrim(str((@reservedpages - @usedpages) * 8192 / 1024.,15,0) + ' KB')
end
December 13, 2006 at 9:38 am
Hi peterhe
How to get the implementation for the system stored procedures??!!!
Thanks for the reply.
Vijay
December 13, 2006 at 10:08 am
select object_definition(ID of your object)
December 13, 2006 at 5:18 pm
Still you can use sp_helptext...
select object_definition(object_id('sp_spaceused'))
exec sp_helptext sp_spaceused
MohammedU
Microsoft SQL Server MVP
December 13, 2006 at 9:48 pm
thanks for the reply guys...that was really helpful and I will let you all know if theres any difference in the implementation of sp_spaceused from 2000....
-Vijay
December 14, 2006 at 3:27 am
Hi
I just need one more clarification....I dont hav the privilege to do a DBCC update...so I cannot use sp_spaceused with @updateusage parameter. therefore can I use the select sum(reserved_page_count) from master.sys.dm_db_partition_stats; to get the current reserved space????
I prefer the dynamic management view because the sys.allocation_units will not return the correct values when there is a drop or truncate on a large table(as given in MSDN)...
regards
Vijay
December 14, 2006 at 3:27 am
Hi All
I just went through the implementation sp_spaceused of SQLServer2005 and I am most certain that they have included the non-clustered indexes also for the space calculation..The non-clustered indexes were left out in the sp_spaceused of the SQLServer2000....
The most interesting part is that whoever has implemented this in 2005 has actually copy->pasted the comments from the 2000 implementation..you can see that there is a comment line saying that
/*
** 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)
*/
But if you really look at the code they are doing select sum(total_pages) from sys.allocation_units; ... they are not filtering the indexes as given in the comment lines...If you hav to filter the indexes u hav to use the sys.dm_db_partition_stats and say index_id in(0,1,255); But that wud fetch the wrong answer.....since there are XML indexes that hav the index id 202 and there are couple of other indexes.
Which brings me to the conclusion that the 2000 implementation of sp_spaceused has a flaw--> it is excluding the non-clustered indexes which should also be considered for calculating the space available...
Please comment on these findings...correct me if I am wrong.....
thanks in advance
Vijay
December 14, 2006 at 10:33 am
The comments, the code difference, the changes ... hmmm ... sounds like large project 'rookie' snafus that fell through the cracks due to lack of diligence and planning of QA.
I DO expect far more from MS.
Just another reason to wait until SP1 has been GA for at least 6 months ...
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
December 14, 2006 at 1:08 pm
My boss asked me yesterday to tell him the size of our largest database and our average database size, so I wrote the following script yesterday that uses sys.database_files to calculate the size of the database.
From my main server (replication publisher) I have linked servers to each of the other servers, I included the Exec sp_msforeachdb line for each server so that I had info for all databases in 1 table varaible.
Declare
@DBSize Table (DBName sysname, DBSize decimal(11, 2))
Insert
Into @DBSize
Exec
sp_msforeachdb 'If ''?'' Not In (''master'',''model'',''tempdb'',''msdb'',''distribution'') Select ''?'', (Sum(size) * 8.00) / 1024.00 From ?.sys.database_files'
Exec
Server2.master.dbo.sp_msforeachdb 'If ''?'' Not In (''master'',''model'',''tempdb'',''msdb'',''distribution'') Select ''?'', (Sum(size) * 8.00) / 1024.00 From ?.sys.database_files'
Select
Max(DBSize), Avg(DBSize) From @DBSize
Select
* From @DBSize Order By DBSize desc
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply