1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43
|
set arithabort off
select s.segment
, "total_space" = convert(float, sum(u.size)) * @@maxpagesize/(1024.0 * 1024.0)
, "free_space" = case when s.segment = 2
then
convert(float, sum(lct_admin("logsegment_freepages", db_id()))) * @@maxpagesize / (1024.0 * 1024.0)
else
convert(float, sum(curunreservedpgs(u.dbid, u.lstart, u.unreservedpgs))) * @@maxpagesize / (1024.0 * 1024.0)
end
, s.name
into #segments
from master..sysusages u
, syssegments s
where u.dbid = db_id()
and (power(2, s.segment) & u.segmap) = power(2, s.segment)
group by s.segment, s.name
select i.id
, "used" = convert(float, reserved_pgs(i.id, i.doampg)) * @@maxpagesize / (1024.0 * 1024.0)
, i.segment
into #used
from sysobjects o
, sysindexes i
where o.id = i.id
and i.indid < 2
and o.sysstat2 & 57344 in (32768, 16384)
select o.name
, i.used
, i.segment
, s.name
, s.free_space
, "pct" = s.free_space / i.used
from sysobjects o
, #used i
, #segments s
where o.id = i.id
and i.segment = s.segment
drop table #segments
drop table #used |
Partager