|
|
Posted by Roy Harvey (SQL Server MVP) on 12/29/07 11:51
Since I see mention in an earlier part of the thread that you are
running on SQL Server 7, here are a couple of system stored procedures
I used in 7 to see how space was being used. Add them to the master
database and you can use them from any database like other system
procs. Note the double underscore in the names differentiates them
from the system procs supplied by Microsoft.
They are really just hacks of sp_spaceused. sp__spaceall shows all
tables together, not one at a time. sp__spaceavail shows the total
space but breaks it down into data and log, which is the only way it
means anything.
Before running them always execute DBCC UPDATEUSAGE(0).
Roy Harvey
Beacon Falls, CT
create procedure sp__spaceall
@objname varchar(92) = '%' -- The object we want size on.
as
declare @type smallint -- The object type.
declare @pages int -- Working variable for size
calc.
declare @dbname varchar(30)
declare @dbsize dec(15,0)
/*
** Check to see that the objname is local.
*/
if @objname IS NOT NULL
begin
if @objname like '%.%.%'
begin
select @dbname =
substring(@objname, 1, charindex('.',
@objname))
if @dbname <> db_name()
begin
raiserror(15250,-1,-1)
return (1)
end
end
else
select @dbname = db_name()
end
/*
** We need to create a temp table to do the calculation.
** reserved: sum(reserved) where indid in (0, 1, 255)
** data: sum(dpages) where indid < 2 + sum(used) where indid = 255
(text)
** indexp: sum(used) where indid in (0, 1, 255) - data
** unused: sum(reserved) - sum(used) where indid in (0, 1, 255)
*/
create table #spt_space
(
name varchar(30) not null,
id int not null,
rows int null,
reserved dec(15) null,
data dec(15) null,
indexp dec(15) null,
unused dec(15) null
)
set nocount on
/*
** We want a particular object.
*/
/*
** Now calculate the summary data.
** reserved: sum(reserved) where indid in (0, 1, 255)
*/
insert into #spt_space (name, id)
select name, id from sysobjects
where name like @objname
and type = 'U'
--and name not like 'sys%'
update #spt_space set reserved = (select sum(reserved)
from sysindexes
where indid in (0, 1, 255)
and sysindexes.id =
#spt_space.id)
/*
** data: sum(dpages) where indid < 2
** + sum(used) where indid = 255 (text)
*/
update #spt_space
set data = (select sum(dpages)
from sysindexes
where indid < 2
and sysindexes.id =
#spt_space.id)
update #spt_space
set data = data + (select isnull(sum(used), 0)
from sysindexes
where indid = 255
and sysindexes.id =
#spt_space.id)
/* index: sum(used) where indid in (0, 1, 255) - data */
update #spt_space
set indexp = (select sum(used)
from sysindexes
where indid in (0, 1, 255)
and sysindexes.id =
#spt_space.id)
- data
/* unused: sum(reserved) - sum(used) where indid in (0, 1,
255) */
update #spt_space
set unused = reserved
- (select sum(used)
from sysindexes
where indid in (0, 1,
255)
and sysindexes.id =
#spt_space.id)
update #spt_space
set rows = (select rows
from sysindexes
where indid < 2
and sysindexes.id =
#spt_space.id)
select Name = substring(#spt_space.name, 1, 24),
Rows = STR(rows, 9, 0),
Reserved = (str(reserved * d.low / 1024.,9,0) +
' ' + 'KB'),
Data = (str(data * d.low / 1024.,8,0) +
' ' + 'KB'),
"Index Size" = (str(indexp * d.low / 1024.,8,0) +
' ' + 'KB'),
Unused = (str(unused * d.low / 1024.,6,0) +
' ' + 'KB')
from #spt_space, master.dbo.spt_values d
where d.number = 1
and d.type = 'E'
order by #spt_space.name
return (0)
GO
GRANT EXECUTE ON dbo.sp__spaceall TO public
GO
CREATE PROC sp__spaceavail
AS
BEGIN
declare @dbsize3 dec(15,0)
declare @dbsize4 dec(15,0)
declare @dbsize dec(15,0)
declare @allocated dec(15,0)
declare @allocated3 dec(15,0)
declare @allocated4 dec(15,0)
declare @unalloc dec(15,0)
declare @unalloc3 dec(15,0)
declare @unalloc4 dec(15,0)
set nocount on
select @dbsize3 = isnull(sum(convert(dec(15),size)),0)
from master..sysusages
where dbid = db_id()
and segmap = 3
select @dbsize4 = isnull(sum(convert(dec(15),size)),0)
from master..sysusages
where dbid = db_id()
and segmap = 4
select @dbsize = isnull(sum(convert(dec(15),size)),0)
from master..sysusages
where dbid = db_id()
select @allocated = sum(convert(dec(15),reserved))
from sysindexes
where indid in (0, 1, 255)
select @allocated3 = sum(convert(dec(15),reserved))
from sysindexes
where indid in (0, 1, 255) and name <> 'syslogs'
select @allocated4 = sum(convert(dec(15),reserved))
from sysindexes
where indid in (0, 1, 255) and name = 'syslogs'
select @unalloc = @dbsize - @allocated,
@unalloc3 = @dbsize3 - @allocated3,
@unalloc4 = @dbsize4 - @allocated4
select database_name = db_name()
print ''
select ' Data:',
'Total (MB)' = str(@dbsize3 / 512,15,2),
'Allocated (MB)' = str(@allocated3/ 512,15,2),
'Unallocated (MB)' = str(@unalloc3 / 512,15,2),
'% Free' = str(@unalloc3 * 100 / @dbsize3,6,2)
where @dbsize3 <> 0
UNION ALL
select ' Log:',
str(@dbsize4 / 512,15,2),
str(@allocated4/ 512,15,2),
str(@unalloc4 / 512,15,2),
str(@unalloc4 * 100 / @dbsize4,6,2)
where @dbsize4 <> 0
UNION ALL
select 'Total:',
str(@dbsize / 512,15,2),
str(@allocated/ 512,15,2),
str(@unalloc / 512,15,2),
str(@unalloc * 100 / @dbsize,6,2)
where @dbsize3 = 0
OR @dbsize4 = 0
END
GO
GRANT EXECUTE ON dbo.sp__spaceavail TO public
GO
Navigation:
[Reply to this message]
|