Sybase: How to query the space usage of a segment directly

Source: Internet
Author: User
Tags mixed sybase

Q: Using sp_helpsegment, you can see the space usage of a segment, but there are all the tables and index information on the paragraph in the result, and for a field that has many tables, it's scary. Want to see directly concerned about the space use of the paragraph, please master pointing?

For:

/*author hobbylu*/
/*reference from Sybase procedures*/
/*2005-07-01*/
CREATE PROCEDURE Sp_helpsegment_me
@segname varchar = NULL/* Segment name */
As
declare @segbit int,/* This is the bit version of the segment # */
@segment int,/* The segment number of the segment * *
@free_pages int,/* Unused pages in segment * *
@factor Float,/* conversion factor to convert to MB * *
@clr_pages int,/* reserved for CLRs * *
@total_pages int,/* Total allocatable log space * *
@used_pages int,/* Allocated log space * *
@ismixedlog INT/* Mixed log & data database? */
If @ @trancount = 0
Begin
Set Chained off
End
Set TRANSACTION ISOLATION Level 1
SET NOCOUNT ON
/*
* * If no segment name given, get ' em all.
*/
If @segname is null
Begin
--adaptive Server has expanded all ' * ' elements in the following statement
Select Syssegments.segment, Syssegments.name, Syssegments.status
From Syssegments ORDER by Segment
Return (0)
End
/*
* * Make sure the segment exists
*/
If not EXISTS (SELECT *
From syssegments
where name = @segname)
Begin
/* 17520, "There is no such segment as '%1! '." * *
RAISERROR 17520, @segname
Return (1)
End
/*
* * Show the syssegment entry, then the fragments and size it are on,
* * then any dependent objects in the database.
*/
--adaptive Server has expanded all ' * ' elements in the following statement
Select Syssegments.segment, Syssegments.name, Syssegments.status
From syssegments
WHERE name = @segname
/*
* * Set the bit position for the segment.
*/
Select @segment = Segment
From syssegments
WHERE name = @segname
/*
* * Now set the segments on @devname sysusages.
*/
if (@segment < 31)
Select @segbit = Power (2, @segment)
Else
/*
* * Since this are segment, power (2) would overflow
* * Since segmap is a int. We ' ll grab the machine-dependent
* * bit mask from spt_values to set the right bit.
*/
Select @segbit = Low
From master.dbo.spt_values
where type = "E"
and number = 2
/*
* * Get factor for conversion of the pages to megabytes from Spt_values
*/
Select @factor = CONVERT (float, low)/1048576.0
From master.dbo.spt_values
where number = 1 and type = "E"
Select @total_pages = SUM (u.size)
From Master.dbo.sysusages u
where U.segmap & @segbit = @segbit
and u.dbid = db_id ()
Select @ismixedlog = Status2 & 32768
From master.dbo.sysdatabases where dbid = db_id ()
/*
* * Select The sizes of the segments
*/
if (@segbit = 4)
Begin
Select device = D.name,
Size = CONVERT (varchar), round (SUM (u.size) * @factor), 0) + MB
From Master.dbo.sysusages u, master.dbo.sysdevices D
where U.segmap & @segbit = @segbit
and u.dbid = db_id ()
and D.status & 2 = 2
and U.vstart between D.low and D.high
Group BY D.name ORDER by D.name
Select @clr_pages = Lct_admin ("Reserved_for_rollbacks", db_id ())
Select @free_pages = Lct_admin ("Logsegment_freepages", db_id ())
-@clr_pages
Select free_pages = @free_pages
if (@ismixedlog = 32768)
Begin
/*
* * For a mixed log and data database, we cannot
* * Deduce the log used space
* * As it is mixed with data. So we take the expensive
* * Way by scanning syslogs.
*/
Select @used_pages = Lct_admin ("Num_logpages", db_id ())
/* Account allocation pages as used pages * *
Select @used_pages = @used_pages + (@total_pages/256)
End
Else
Begin
/* Dedicated LOG database * *
Select @used_pages = @total_pages-@free_pages
-@clr_pages
End
End
Else
Begin
Select device = D.name,
Size = CONVERT (varchar), round (SUM (u.size) * @factor), 0) + "MB",
free_pages = SUM (Curunreservedpgs (db_id (), U.lstart, U.unreservedpgs))
From Master.dbo.sysusages u, master.dbo.sysdevices D
where U.segmap & @segbit = @segbit
and u.dbid = db_id ()
and D.status & 2 = 2
and U.vstart between D.low and D.high
Group BY D.name ORDER by D.name
Select @free_pages = SUM (Curunreservedpgs (db_id (), U.lstart, U.unreservedpgs))
From Master.dbo.sysusages u
where U.segmap & @segbit = @segbit
and u.dbid = db_id ()
Select @used_pages = @total_pages-@free_pages
Select @clr_pages = 0
End
/*
* * Select the dependent objects
*/
/*
* * Print total_size, total_pages, Free_pages, Used_pages and Reserved_pages
*/
Select total_size = CONVERT (varchar (15),
Round (@total_pages * @factor, 0)) + "MB",
Total_pages = Convert (char, @total_pages),
Free_pages = Convert (char, @free_pages),
Used_pages = Convert (char, @used_pages),
Reserved_pages = Convert (char, @clr_pages)
Return (0)

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.