IF OBJECT_ID('dbo.sp_dba_dbspace') IS NOT NULL
BEGIN
DROP PROCEDURE dbo.sp_dba_dbspace
IF OBJECT_ID('dbo.sp_dba_dbspace') IS NOT NULL
PRINT '<<<>>>'
ELSE
PRINT '<<<>>>'
END
go
create procedure sp_dba_dbspace
as
set nocount on
declare @data_size float,
@log_size float,
@psize_kb float,
@log_id int,
@rsrvTTL int,
@log_segment int,
@message char(80)
/* get the segmap mask for log only segments */
select @log_segment = number
from master..spt_values
where name = "log only"
if @@error != 0 return
/* get the id of the syslogs table */
select @log_id = id
from sysobjects where name = "syslogs"
if @@error != 0 return
/* get page size from system proc values table */
select distinct @psize_kb = convert(float, low) / 1024
from master..spt_values
where number = 1 and type = "E"
if @@error != 0 return
/* get the db size excluding the log segment size */
select distinct @data_size = sum(size)
from master..sysusages
where dbid = db_id() and segmap != @log_segment
if @@error != 0 return
/* get the log segment size */
select distinct @log_size = sum(size)
from master..sysusages
where dbid = db_id() and segmap = @log_segment
if @@error != 0 return
if @log_size is NULL
select @log_size = 0 /* case where the db has no log segment */
/* print out the overall database stats */
select @message = " " + replicate("=",68)
print @message
select DbName = rtrim(db_name()),
DbSize = ltrim(str(sum(@data_size + @log_size) * @psize_kb, 12, 0) + " KB"),
DataSegments = ltrim(str(@data_size * @psize_kb, 10, 0) + " KB"),
LogSegments = ltrim(str(@log_size * @psize_kb, 10, 0) + " KB")
if @@error != 0 return
select @message = " " + replicate("=",68)
print @message
/* get page counts for all tables except syslogs */
select
o.name "Tname",
i.name "Iname",
i.segment,
i.id,
i.indid,
Rrows = rowcnt(i.doampg),
rsrv = (reserved_pgs(i.id,i.doampg) + reserved_pgs(i.id,i.ioampg)),
data = data_pgs(i.id,i.doampg),
idx = data_pgs(i.id,i.ioampg),
unused = ((reserved_pgs(i.id,i.doampg) + reserved_pgs(i.id,i.ioampg)) -
(data_pgs(i.id,i.doampg) + data_pgs(i.id,i.ioampg)))
into #tableCount
from sysobjects o, sysindexes i
where o.id = i.id
and o.id != @log_id
if @@error != 0 return
/* print out results
* order by system tables, system tables nonclustered idx
* then user tables with no clustered idx, clustered idx tabs and
* finally non clustered idx
*/
print " SYSTEM TABLES"
select @message = " " + replicate("=",68)
print @message
select rtrim(Tname) "TableName",
rtrim(Iname) "IndexName",
ltrim(str(convert(float,Rrows),12,0)) "RowCount",
ltrim(str(convert(float,rsrv),10,0)) "ReservedPages",
ltrim(str(convert(float,rsrv)*@psize_kb,10,2)+" KB") "Space",
ltrim(str(convert(float,data),10,0)) "DataPages",
ltrim(str(convert(float,idx),10,0)) "IndexPages",
ltrim(str(convert(float,unused),10,0)) "UnusedPages"
from #tableCount tp
where tp.id <>
and ( tp.indid = 0 or tp.indid = 1 )
order by id
select @message = " " + replicate("=",68)
print @message
print " NON CLUSTERED INDEXES ON SYSTEM TABLES"
select @message = " " + replicate("=",68)
print @message
select rtrim(Tname) "TableName",
rtrim(Iname) "IndexName",
ltrim(str(convert(float,rsrv),10,0)) "ReservedPages",
ltrim(str(convert(float,rsrv)*@psize_kb,10,2)+" KB") "Space",
ltrim(str(convert(float,idx),10,0)) "IndexPages",
ltrim(str(convert(float,unused),10,0)) "UnusedPages"
from #tableCount tp
where tp.id <>
and tp.indid > 1
order by id
select @message = " " + replicate("=",68)
print @message
print " USER TABLES WITH NO CLUSTERED INDEXES"
select @message = " " + replicate("=",68)
print @message
select rtrim(Tname) "TableName",
ltrim(str(convert(float,Rrows),12,0)) "RowCount",
ltrim(str(convert(float,rsrv),10,0)) "ReservedPages",
ltrim(str(convert(float,rsrv)*@psize_kb,10,2)+" KB") "Space",
ltrim(str(convert(float,data),10,0)) "DataPages",
ltrim(str(convert(float,unused),10,0)) "UnusedPages"
from #tableCount tp
where tp.id > 100
and tp.indid = 0
order by Tname
select @message = " " + replicate("=",68)
print @message
print " USER TABLES WITH CLUSTERED INDEX"
select @message = " " + replicate("=",68)
print @message
select rtrim(Tname) "TableName",
rtrim(Iname) "IndexName",
ltrim(str(convert(float,Rrows),12,0)) "RowCount",
ltrim(str(convert(float,rsrv),10,0)) "ReservedPages",
ltrim(str(convert(float,rsrv)*@psize_kb,10,2)+" KB") "Space",
ltrim(str(convert(float,data),10,0)) "DataPages",
ltrim(str(convert(float,idx),10,0)) "IndexPages",
ltrim(str(convert(float,unused),10,0)) "UnusedPages"
from #tableCount tp
where tp.id > 100
and tp.indid = 1
order by Tname
select @message = " " + replicate("=",68)
print @message
print " NON CLUSTERED INDEXES ON USER TABLES"
select @message = " " + replicate("=",68)
print @message
select rtrim(Tname) "TableName",
rtrim(Iname) "IndexName",
ltrim(str(convert(float,rsrv),12,0)) "ReservedPages",
ltrim(str(convert(float,rsrv)*@psize_kb,10,2)+" KB") "Space",
ltrim(str(convert(float,idx),10,0)) "IndexPages",
ltrim(str(convert(float,unused),10,0)) "UnusedPages"
from #tableCount tp
where tp.id > 100
and tp.indid > 1
order by Tname
/* now get space for syslogs */
insert into #tableCount
select
o.name,
i.name,
i.segment,
i.id,
i.indid,
rowcnt(i.doampg),
reserved_pgs(i.id, i.doampg),
data_pgs(i.id,i.doampg),
0,
unused = reserved_pgs(i.id, i.doampg) - data_pgs(i.id,i.doampg)
from sysobjects o, sysindexes i
where o.id = i.id
and o.id = @log_id
if @@error != 0 return
/* compute summary results showing Total data seg allocated
* whats been reserved and unused.
* compute a usage as a percentage
*/
select @message = " " + replicate("=",68)
print @message
print " SUMMARY RESULTS "
/* if log on its own segment then use @log_size else use data_size */
if @log_size != 0
begin
select
DataSegTTL = ltrim(str(@data_size * @psize_kb, 10, 0) + " KB"),
Reserved = ltrim(str(convert(float,sum(rsrv))*@psize_kb,10,0)+" KB"),
Objects = ltrim(str(convert(float,sum(data))*@psize_kb,10,0)+" KB"),
Indixes = ltrim(str(convert(float,sum(idx))*@psize_kb,10,0)+" KB"),
FreeSpace =str((@data_size*@psize_kb)-(sum(rsrv)*@psize_kb)) + " KB",
Usage = str((sum(rsrv)*@psize_kb)/(@data_size * @psize_kb) * 100,6,2) + " % Used"
from #tableCount
where id != @log_id
if @@error != 0 return
select
LogSegTTL = str(@log_size * @psize_kb, 10, 0) + " KB",
Reserved = str(rsrv*@psize_kb) + " KB",
FreeSpace = str((@log_size*@psize_kb)-(rsrv*@psize_kb)) + " KB",
Usage = str((((rsrv*@psize_kb)/(@log_size * @psize_kb)) * 100),6,2) + " % Used"
from #tableCount
where id = @log_id
if @@error != 0 return
end
else
begin
select @message = "DATA AND LOG SHARE SAME SEGMENT"
print @message
/* get the total of reserved pages from #tableCount */
select @rsrvTTL = sum(rsrv) from #tableCount
if @@error != 0 return
select
DataSegTTL = ltrim(str(@data_size * @psize_kb, 10, 0) + " KB"),
Reserved = ltrim(str(convert(float,sum(rsrv))*@psize_kb,10,0)+" KB"),
Objects = ltrim(str(convert(float,sum(data))*@psize_kb,10,0)+" KB"),
Indixes = ltrim(str(convert(float,sum(idx))*@psize_kb,10,0)+" KB"),
FreeSpace = str((@data_size*@psize_kb)-(@rsrvTTL*@psize_kb)) + " KB",
Usage = str((@rsrvTTL*@psize_kb)/(@data_size * @psize_kb) * 100,6,2) + " % Used"
from #tableCount
where id != @log_id
select
DataSegTTL = str(@data_size * @psize_kb, 10, 0) + " KB",
LogReserved = str(rsrv*@psize_kb) + " KB",
FreeSpace = str((@data_size*@psize_kb)-(@rsrvTTL*@psize_kb)) + " KB",
Usage = str((((@rsrvTTL*@psize_kb)/(@data_size * @psize_kb)) * 100),6,2) + " % Used"
from #tableCount
where id = @log_id
if @@error != 0 return
end
select @message = " " + replicate("=",68)
print @message
select @message = "TOTAL SPACE RESERVED PER SEGMENT"
print @message
select
s.name "SegName",
Reserved = ltrim(str(convert(float,sum(rsrv))*@psize_kb,10,0)+" KB")
from syssegments s, #tableCount t
where s.segment = t.segment
group by s.name
order by s.segment
select @message = " " + replicate("=",68)
print @message
/* how much space the current db uses on each device */
select size = convert(float, high-low+1) * @psize_kb,
alloc = convert(float, sum(size)) * @psize_kb,
device = name,
total_alloc = convert(float, 0)
into #devSum
from master..sysusages, master..sysdevices
where vstart >= low and vstart <= high and cntrltype = 0
and dbid = db_id()
group by name
if @@error != 0 return
/* update total usage for each device. */
update #devSum
set total_alloc = ( select sum(su.size) from master..sysusages su,
master..sysdevices sd
where vstart >= low and vstart <= high and cntrltype = 0
and #devSum.device = sd.name
)
if @@error != 0 return
update #devSum
set total_alloc = total_alloc * @psize_kb
if @@error != 0 return
/* add summary lines to the device sum table */
insert #devSum
select sum(size), sum(alloc), "TOTAL", sum(total_alloc)
from #devSum
if @@error != 0 return
/* now print stats */
select @message = " DISK ACTUAL USAGE BY DEVICE FOR: " + db_name()
print @message
select @message = " " + replicate("=",68)
print @message
select DeviceName = device,
Size = str(size, 10, 0) + " KB",
ThisDb = str(alloc, 10, 0) + " KB",
OtherDb = str(total_alloc - alloc, 10, 0) + " KB",
UnAlloc = str(size - total_alloc, 10 ,0) + " KB",
Usage = str(total_alloc/size *100,6,2) + " % Used"
from #devSum
where device != "TOTAL"
order by device
if @@error != 0 return
select @message = " " + replicate("=",68)
print @message
select TOTAL = device,
Size = str(size, 10, 0) + " KB",
ThisDb = str(alloc, 10, 0) + " KB",
OtherDb = str(total_alloc - alloc, 10, 0) + " KB",
UnAlloc = str(size - total_alloc, 10 ,0) + " KB",
Usage = str(total_alloc/size *100,6,2) + " % Used"
from #devSum
where device = "TOTAL"
if @@error != 0 return
select @message = " " + replicate("=",68)
print @message
drop table #devSum
drop table #tableCount
return 0
go
EXEC sp_procxmode 'dbo.sp_dba_dbspace','unchained'
go
IF OBJECT_ID('dbo.sp_dba_dbspace') IS NOT NULL
PRINT '<<<>>>'
ELSE
PRINT '<<<>>>'
go
USE sybsystemprocs
go
GRANT EXECUTE ON dbo.sp_dba_dbspace TO public
go
No comments:
Post a Comment