January 7, 2004 at 9:39 am
Can someone please help me correct this script to show the correct total size (MB) of each table/index. It is a popular script I grabbed somewhere online, which shows output like...
Table Rows r/pg Dpages Upages DataMb IxMb
Orders 325329 13 24097 26300 188.3 17.2
The DataMB value is always less than what is shown in Enterprise Mgr's Taskpad view or by the sp_msforeachtable 'sp_spaceused "?"' command, both of which match each other and I assume are correct. Perhaps the script subtracts free space? Thanks for any help!
-- Description: Display user tables and sizes
select CAST(User_Name(o.uid) AS varchar(08)) Owner ,
CAST(o.name AS varchar(30)) [Table],
str(rows,8,0) Rows ,
case dpages
WHEN 0 THEN str(0 ,4,0) ELSE str(rows/dpages,4,0)
END [r/pg] ,
str(dpages,8,0) Dpages ,str(used,8,0) Upages ,
str(dpages/128.0,6,1) DataMb ,
str((used-dpages)/128.0,6,1) IxMb ,
groupname FileGroup
FROM sysindexes i
INNER JOIN sysobjects o ON o.id = i.id
INNER JOIN sysfilegroups f ON f.groupid = i.groupid
where indid < 2
and o.type in ('U', 'S')
-- and used > 0
-- and o.name not like 'pbsys%'
and o.name not like 'sys%'
order by Owner, Dpages desc, used desc, Rows desc, [Table]
smv929
January 8, 2004 at 7:55 am
I would look at dbcc updateusage. This could be the reason the result sets are different.
Tom
January 8, 2004 at 6:37 pm
Try looking at the TSQL for sp_spaceused and see where your script might be off. Also remember that if you haven't updated your statistics sysindexes might not be as accurate as you would like.
Gary Johnson
Microsoft Natural Language Group
DBA, Sr. DB Engineer
This posting is provided "AS IS" with no warranties, and confers no rights. The opinions expressed in this post are my own and may not reflect that of my employer.
January 9, 2004 at 9:58 am
I will try running DBCC UPDATEUSAGE (0) at the beginning of the script. I think you guys are correct although I can't confirm because that db is remote and unaccessible to me currently. By the way, the following script returned the correct MB values on the same datbase. Notice it uses sp_spacedused. Thanks!
set nocount on
declare @STR varchar(100)
declare @sql varchar(100)
declare @data int
declare @sort int
--Sets sortation value
set @sort = 3
--Create Temp Table
create table #TempTable
(
[Table_Name] varchar(50),
Row_Count int,
Table_Size varchar(50),
Data_Space_Used varchar(50),
Index_Space_Used varchar(50),
Unused_Space varchar(50)
)
create table #TempTable2
(
[Table_Name] varchar(50),
Row_Count int,
Table_Size int,
Data_Space_Used int,
Index_Space_Used int,
Unused_Space int
)
--Create Stored Procedure String
set @STR = 'sp_msforeachtable ''sp_spaceused "?"'''
--Populate Temp Table
insert into #TempTable exec(@str)
--Determin Sort Order
set @sql =
case
when @sort = '1' then '
select *
from #TempTable2
order by Table_Name
'
when @sort = '2' then '
select *
from #TempTable2
order by Row_Count desc
'
when @sort = '3' then '
select *
from #TempTable2
order by Table_Size desc
'
when @sort = '4' then '
select *
from #TempTable2
order by Index_Space_Used desc
'
else '
select *
from #TempTable2
order by Unused_Space desc
'
end
insert into #TempTable2
select Table_Name, Row_Count, cast((substring(Table_Size, 1, (len(Table_Size)-3))) as int) ,
cast((substring(Data_Space_Used, 1, (len(Data_Space_Used)-3)))as int) ,
cast((substring(Index_Space_Used, 1, (len(Index_Space_Used)-3))) as int),
cast((substring(Unused_Space, 1, (len(Unused_Space)-3))) as int)
from #TempTable
exec(@sql)
--Delete Temporay Table
drop table #TempTable
drop table #TempTable2
smv929
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply