Sybase NNTP forums - End Of Life (EOL)

The NNTP forums from Sybase - forums.sybase.com - are now closed.

All new questions should be directed to the appropriate forum at the SAP Community Network (SCN).

Individual products have links to the respective forums on SCN, or you can go to SCN and search for your product in the search box (upper right corner) to find your specific developer center.

Log segment total size.

10 posts in General Discussion Last posting was on 2012-08-01 07:41:59.0Z
Eyal Posted on 2012-07-11 07:17:01.0Z
Sender: 7f44.4ffd247a.1804289383@sybase.com
From: Eyal
Newsgroups: sybase.public.ase.general
Subject: Log segment total size.
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4ffd286d.7fdf.1681692777@sybase.com>
NNTP-Posting-Host: 172.20.134.41
X-Original-NNTP-Posting-Host: 172.20.134.41
Date: 11 Jul 2012 00:17:01 -0700
X-Trace: forums-1-dub 1341991021 172.20.134.41 (11 Jul 2012 00:17:01 -0700)
X-Original-Trace: 11 Jul 2012 00:17:01 -0700, 172.20.134.41
Lines: 34
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:31238
Article PK: 74127

Hi All
I am new to the Sybase world.
When running "sp_helpsegment logsegment" at master database
I get total_size = 13MB
When I run the SQL "select sum(size/512) "total_size" from
sysusages" I get total_size = 156
When I run the SQL "select sum(size/512) "total_size" from
sysusages where db_name(dbid)='master'" I get total_size =
13
I search the Web and find this query.
"select substring (S.name,1,20) as segment_name,
sum (U.size) / (512 / (@@maxpagesize / 2048)) as total_size,
sum(curunreservedpgs(db_id(), U.lstart, 0)) / (512 /
(@@maxpagesize / 2048)) as free_space_MB,
100* ( sum(curunreservedpgs(db_id(), U.lstart, 0)) / (512 /
(@@maxpagesize / 2048)) ) / ( sum (U.size) / (512 /
(@@maxpagesize / 2048)) )
as free_space_PCT
from master..syssegments S,
master..sysusages U,
master..sysdevices D
where dbid = db_id()
and U.vdevno = D.vdevno
and vstart between low and high
and power (2,S.segment) & U.segmap > 0
and substring (S.name,1,20) = 'logsegment'
group by S.name
order by S.name"

This return total_size = 13.

So , Please help me to understand what is going on.
Thanks
Eyal


"Mark A. Parsons" <iron_horse Posted on 2012-07-11 12:22:57.0Z
From: "Mark A. Parsons" <iron_horse@no_spamola.compuserve.com>
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 6.1; en-US; rv:1.9.2.13) Gecko/20101207 Lightning/1.0b2 Thunderbird/3.1.7
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Re: Log segment total size.
References: <4ffd286d.7fdf.1681692777@sybase.com>
In-Reply-To: <4ffd286d.7fdf.1681692777@sybase.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
X-Antivirus: avast! (VPS 120603-1, 06/03/2012), Outbound message
X-Antivirus-Status: Clean
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4ffd7021$1@forums-1-dub>
Date: 11 Jul 2012 05:22:57 -0700
X-Trace: forums-1-dub 1342009377 172.20.134.152 (11 Jul 2012 05:22:57 -0700)
X-Original-Trace: 11 Jul 2012 05:22:57 -0700, vip152.sybase.com
Lines: 40
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:31239
Article PK: 74128

What's your question?

Looks like your master database is 13MB in size, and (most likely) all of your segments in the master database are also
13MB in size (ie, all segments are sitting on all devices - seep output of 'sp_helpdb master').

On 07/11/2012 01:17, Eyal wrote:
> Hi All
> I am new to the Sybase world.
> When running "sp_helpsegment logsegment" at master database
> I get total_size = 13MB
> When I run the SQL "select sum(size/512) "total_size" from
> sysusages" I get total_size = 156
> When I run the SQL "select sum(size/512) "total_size" from
> sysusages where db_name(dbid)='master'" I get total_size =
> 13
> I search the Web and find this query.
> "select substring (S.name,1,20) as segment_name,
> sum (U.size) / (512 / (@@maxpagesize / 2048)) as total_size,
> sum(curunreservedpgs(db_id(), U.lstart, 0)) / (512 /
> (@@maxpagesize / 2048)) as free_space_MB,
> 100* ( sum(curunreservedpgs(db_id(), U.lstart, 0)) / (512 /
> (@@maxpagesize / 2048)) ) / ( sum (U.size) / (512 /
> (@@maxpagesize / 2048)) )
> as free_space_PCT
> from master..syssegments S,
> master..sysusages U,
> master..sysdevices D
> where dbid = db_id()
> and U.vdevno = D.vdevno
> and vstart between low and high
> and power (2,S.segment)& U.segmap> 0
> and substring (S.name,1,20) = 'logsegment'
> group by S.name
> order by S.name"
>
> This return total_size = 13.
>
> So , Please help me to understand what is going on.
> Thanks
> Eyal


Eyal Posted on 2012-07-11 13:49:00.0Z
Sender: 104d.4ffd800d.1804289383@sybase.com
From: Eyal
Newsgroups: sybase.public.ase.general
Subject: Re: Log segment total size.
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4ffd844c.110c.1681692777@sybase.com>
References: <4ffd7021$1@forums-1-dub>
NNTP-Posting-Host: 172.20.134.41
X-Original-NNTP-Posting-Host: 172.20.134.41
Date: 11 Jul 2012 06:49:00 -0700
X-Trace: forums-1-dub 1342014540 172.20.134.41 (11 Jul 2012 06:49:00 -0700)
X-Original-Trace: 11 Jul 2012 06:49:00 -0700, 172.20.134.41
Lines: 45
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:31240
Article PK: 74129

My question is what are the other SQL query return?
Why the outputs are different ?

> What's your question?
>
> Looks like your master database is 13MB in size, and (most
> likely) all of your segments in the master database are
> also 13MB in size (ie, all segments are sitting on all
> devices - seep output of 'sp_helpdb master').
>
> On 07/11/2012 01:17, Eyal wrote:
> > Hi All
> > I am new to the Sybase world.
> > When running "sp_helpsegment logsegment" at master
> > database I get total_size = 13MB
> > When I run the SQL "select sum(size/512) "total_size"
> > from sysusages" I get total_size = 156
> > When I run the SQL "select sum(size/512) "total_size"
> > from sysusages where db_name(dbid)='master'" I get
> > total_size = 13
> > I search the Web and find this query.
> > "select substring (S.name,1,20) as segment_name,
> > sum (U.size) / (512 / (@@maxpagesize / 2048)) as
> > total_size, sum(curunreservedpgs(db_id(), U.lstart, 0))
> > / (512 / (@@maxpagesize / 2048)) as free_space_MB,
> > 100* ( sum(curunreservedpgs(db_id(), U.lstart, 0)) /
> > (512 / (@@maxpagesize / 2048)) ) / ( sum (U.size) / (512
> > / (@@maxpagesize / 2048)) )
> > as free_space_PCT
> > from master..syssegments S,
> > master..sysusages U,
> > master..sysdevices D
> > where dbid = db_id()
> > and U.vdevno = D.vdevno
> > and vstart between low and high
> > and power (2,S.segment)& U.segmap> 0
> > and substring (S.name,1,20) = 'logsegment'
> > group by S.name
> > order by S.name"
> >
> > This return total_size = 13.
> >
> > So , Please help me to understand what is going on.
> > Thanks
> > Eyal


Bret Halford Posted on 2012-07-11 15:22:09.0Z
From: Bret Halford <bret@sybase.com>
User-Agent: Mozilla/5.0 (Windows NT 5.1; rv:13.0) Gecko/20120614 Thunderbird/13.0.1
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Re: Log segment total size.
References: <4ffd286d.7fdf.1681692777@sybase.com>
In-Reply-To: <4ffd286d.7fdf.1681692777@sybase.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4ffd9a21$1@forums-1-dub>
Date: 11 Jul 2012 08:22:09 -0700
X-Trace: forums-1-dub 1342020129 172.20.134.152 (11 Jul 2012 08:22:09 -0700)
X-Original-Trace: 11 Jul 2012 08:22:09 -0700, vip152.sybase.com
Lines: 52
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:31241
Article PK: 74130


On 7/11/2012 1:17 AM, Eyal wrote:
> Hi All
> I am new to the Sybase world.
> When running "sp_helpsegment logsegment" at master database
> I get total_size = 13MB

That would be the size of the all master database device
fragments that have the log segment.

> When I run the SQL "select sum(size/512) "total_size" from
> sysusages" I get total_size = 156

That would the the combined size of all your databases on
all devices, assuming a 2K page size server. (512 2k pages
= 1MB).

> When I run the SQL "select sum(size/512) "total_size" from
> sysusages where db_name(dbid)='master'" I get total_size =
> 13

That would the be size of all master database device fragments,
assuming a 2k page size server.



> I search the Web and find this query.
> "select substring (S.name,1,20) as segment_name,
> sum (U.size) / (512 / (@@maxpagesize / 2048)) as total_size,
> sum(curunreservedpgs(db_id(), U.lstart, 0)) / (512 /
> (@@maxpagesize / 2048)) as free_space_MB,
> 100* ( sum(curunreservedpgs(db_id(), U.lstart, 0)) / (512 /
> (@@maxpagesize / 2048)) ) / ( sum (U.size) / (512 /
> (@@maxpagesize / 2048)) )
> as free_space_PCT
> from master..syssegments S,
> master..sysusages U,
> master..sysdevices D
> where dbid = db_id()
> and U.vdevno = D.vdevno
> and vstart between low and high
> and power (2,S.segment) & U.segmap > 0
> and substring (S.name,1,20) = 'logsegment'
> group by S.name
> order by S.name"
>
> This return total_size = 13.

That would be the size of all device fragments with the log
segment belonging to the current database. It includes
logic to deal with different page sizes.


Eyal Posted on 2012-07-12 06:05:26.0Z
Sender: 104d.4ffd800d.1804289383@sybase.com
From: Eyal
Newsgroups: sybase.public.ase.general
Subject: Re: Log segment total size.
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4ffe6926.30a8.1681692777@sybase.com>
References: <4ffd9a21$1@forums-1-dub>
NNTP-Posting-Host: 172.20.134.41
X-Original-NNTP-Posting-Host: 172.20.134.41
Date: 11 Jul 2012 23:05:26 -0700
X-Trace: forums-1-dub 1342073126 172.20.134.41 (11 Jul 2012 23:05:26 -0700)
X-Original-Trace: 11 Jul 2012 23:05:26 -0700, 172.20.134.41
Lines: 56
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:31242
Article PK: 74131

Thanks very much.
That clear the picture for me.

> On 7/11/2012 1:17 AM, Eyal wrote:
> > Hi All
> > I am new to the Sybase world.
> > When running "sp_helpsegment logsegment" at master
> > database I get total_size = 13MB
>
> That would be the size of the all master database device
> fragments that have the log segment.
>
> > When I run the SQL "select sum(size/512) "total_size"
> > from sysusages" I get total_size = 156
>
> That would the the combined size of all your databases on
> all devices, assuming a 2K page size server. (512 2k
> pages = 1MB).
>
> > When I run the SQL "select sum(size/512) "total_size"
> > from sysusages where db_name(dbid)='master'" I get
> > total_size = 13
>
> That would the be size of all master database device
> fragments, assuming a 2k page size server.
>
>
>
> > I search the Web and find this query.
> > "select substring (S.name,1,20) as segment_name,
> > sum (U.size) / (512 / (@@maxpagesize / 2048)) as
> > total_size, sum(curunreservedpgs(db_id(), U.lstart, 0))
> > / (512 / (@@maxpagesize / 2048)) as free_space_MB,
> > 100* ( sum(curunreservedpgs(db_id(), U.lstart, 0)) /
> > (512 / (@@maxpagesize / 2048)) ) / ( sum (U.size) / (512
> > / (@@maxpagesize / 2048)) )
> > as free_space_PCT
> > from master..syssegments S,
> > master..sysusages U,
> > master..sysdevices D
> > where dbid = db_id()
> > and U.vdevno = D.vdevno
> > and vstart between low and high
> > and power (2,S.segment) & U.segmap > 0
> > and substring (S.name,1,20) = 'logsegment'
> > group by S.name
> > order by S.name"
> >
> > This return total_size = 13.
>
> That would be the size of all device fragments with the
> log segment belonging to the current database. It
> includes logic to deal with different page sizes.
>
>


Eyal Posted on 2012-07-31 06:25:48.0Z
Sender: 7cdf.50177769.846930886@sybase.com
From: Eyal
Newsgroups: sybase.public.ase.general
Subject: Re: Log segment total size.
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <50177a6c.7d79.1681692777@sybase.com>
References: <4ffe6926.30a8.1681692777@sybase.com>
NNTP-Posting-Host: 172.20.134.41
X-Original-NNTP-Posting-Host: 172.20.134.41
Date: 30 Jul 2012 23:25:48 -0700
X-Trace: forums-1-dub 1343715948 172.20.134.41 (30 Jul 2012 23:25:48 -0700)
X-Original-Trace: 30 Jul 2012 23:25:48 -0700, 172.20.134.41
Lines: 63
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:31282
Article PK: 74170

The question is regarding the total size of the log segment.

When running "sp_helpsegment logsegment" we get :
At DB1
total_size total_pages free_pages used_pages
reserved_pages
----------------- --------------- ---------------
--------------- ---------------
100.0MB 25600 22156 106
0
At DB2
total_size total_pages free_pages used_pages
reserved_pages
----------------- --------------- ---------------
--------------- ---------------
100.0MB 51200 47693 206
0

Which mean that total size is 100 MB

But when running the query that should return the same , we
have got different value.

The query is :

select substring (S.name,1,20) as segment_name,
sum (U.size) / (512 / (@@maxpagesize / 2048)) as total_size,
sum(curunreservedpgs(db_id(), U.lstart, 0)) / (512 /
(@@maxpagesize / 2048)) as free_space_MB,
100* ( sum(curunreservedpgs(db_id(), U.lstart, 0)) / (512 /
(@@maxpagesize / 2048)) ) / ( sum (U.size) / (512 /
(@@maxpagesize / 2048)) )
as free_space_PCT
from master..syssegments S,
master..sysusages U,
master..sysdevices D
where dbid = db_id()
and U.vdevno = D.vdevno
and vstart between low and high
and power (2,S.segment) & U.segmap > 0
and substring (S.name,1,20) = 'logsegment'
group by S.name
order by S.name

At DB1 we got total zise 152 MB

segment_name total_size free_space_MB
free_space_PCT
-------------------- ----------- -------------
--------------
logsegment 152 112
73

At DB2 we got 300 MB

segment_name total_size free_space_MB
free_space_PCT
-------------------- ----------- -------------
--------------
logsegment 300 279
93

Why we got different values?


"Mark A. Parsons" <iron_horse Posted on 2012-07-31 13:05:53.0Z
From: "Mark A. Parsons" <iron_horse@no_spamola.compuserve.com>
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 6.1; en-US; rv:1.9.2.13) Gecko/20101207 Lightning/1.0b2 Thunderbird/3.1.7
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Re: Log segment total size.
References: <4ffe6926.30a8.1681692777@sybase.com> <50177a6c.7d79.1681692777@sybase.com>
In-Reply-To: <50177a6c.7d79.1681692777@sybase.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
X-Antivirus: avast! (VPS 120603-1, 06/03/2012), Outbound message
X-Antivirus-Status: Clean
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <5017d831$1@forums-1-dub>
Date: 31 Jul 2012 06:05:53 -0700
X-Trace: forums-1-dub 1343739953 172.20.134.152 (31 Jul 2012 06:05:53 -0700)
X-Original-Trace: 31 Jul 2012 06:05:53 -0700, vip152.sybase.com
Lines: 72
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:31285
Article PK: 74173

Way too many issues/problems with your query to even try fixing it (eg, why the reference to sysdevices? why the join
criteria on vdevno *and* vstart/low/high? why the hardcoded 512's and 2048's?). I'd suggest you take a look at the
source code for sp_helpsegment; from this you should be able to come up with a better/correct means of calculating
segment space usage.

For a simple calculation of total space allocated... add up sysusages.size for the segment and multiply by @@maxpagesize
to get total *bytes*; divide by 1024 to get total KBs; divide by 1024*1024 to get total MBs.

On 07/31/2012 00:25, Eyal wrote:
> The question is regarding the total size of the log segment.
>
> When running "sp_helpsegment logsegment" we get :
> At DB1
> total_size total_pages free_pages used_pages
> reserved_pages
> ----------------- --------------- ---------------
> --------------- ---------------
> 100.0MB 25600 22156 106
> 0
> At DB2
> total_size total_pages free_pages used_pages
> reserved_pages
> ----------------- --------------- ---------------
> --------------- ---------------
> 100.0MB 51200 47693 206
> 0
>
> Which mean that total size is 100 MB
>
> But when running the query that should return the same , we
> have got different value.
>
> The query is :
>
> select substring (S.name,1,20) as segment_name,
> sum (U.size) / (512 / (@@maxpagesize / 2048)) as total_size,
> sum(curunreservedpgs(db_id(), U.lstart, 0)) / (512 /
> (@@maxpagesize / 2048)) as free_space_MB,
> 100* ( sum(curunreservedpgs(db_id(), U.lstart, 0)) / (512 /
> (@@maxpagesize / 2048)) ) / ( sum (U.size) / (512 /
> (@@maxpagesize / 2048)) )
> as free_space_PCT
> from master..syssegments S,
> master..sysusages U,
> master..sysdevices D
> where dbid = db_id()
> and U.vdevno = D.vdevno
> and vstart between low and high
> and power (2,S.segment)& U.segmap> 0
> and substring (S.name,1,20) = 'logsegment'
> group by S.name
> order by S.name
>
> At DB1 we got total zise 152 MB
>
> segment_name total_size free_space_MB
> free_space_PCT
> -------------------- ----------- -------------
> --------------
> logsegment 152 112
> 73
>
> At DB2 we got 300 MB
>
> segment_name total_size free_space_MB
> free_space_PCT
> -------------------- ----------- -------------
> --------------
> logsegment 300 279
> 93
>
> Why we got different values?


Eyal Posted on 2012-07-31 14:04:33.0Z
Sender: d0b.5017e3a0.846930886@sybase.com
From: Eyal
Newsgroups: sybase.public.ase.general
Subject: Re: Log segment total size.
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <5017e5f1.d4c.1681692777@sybase.com>
References: <5017d831$1@forums-1-dub>
NNTP-Posting-Host: 172.20.134.41
X-Original-NNTP-Posting-Host: 172.20.134.41
Date: 31 Jul 2012 07:04:33 -0700
X-Trace: forums-1-dub 1343743473 172.20.134.41 (31 Jul 2012 07:04:33 -0700)
X-Original-Trace: 31 Jul 2012 07:04:33 -0700, 172.20.134.41
Lines: 85
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:31286
Article PK: 74175

Thanks.
This is something I found over the Web.
I am to green to understand it.
where can I find the source of sp_helpsegment ?
The goal is to get the log segment total size.
It have to be true on any page size and for ASE 12 and 15.

> Way too many issues/problems with your query to even try
> fixing it (eg, why the reference to sysdevices? why the
> join criteria on vdevno *and* vstart/low/high? why the
> hardcoded 512's and 2048's?). I'd suggest you take a look
> at the source code for sp_helpsegment; from this you
> should be able to come up with a better/correct means of
> calculating segment space usage.
>
> For a simple calculation of total space allocated... add
> up sysusages.size for the segment and multiply by
> @@maxpagesize to get total *bytes*; divide by 1024 to get
> total KBs; divide by 1024*1024 to get total MBs.
>
> On 07/31/2012 00:25, Eyal wrote:
> > The question is regarding the total size of the log
> segment. >
> > When running "sp_helpsegment logsegment" we get :
> > At DB1
> > total_size total_pages free_pages
> > used_pages reserved_pages
> > ----------------- --------------- ---------------
> > --------------- ---------------
> > 100.0MB 25600 22156 106
> > 0
> > At DB2
> > total_size total_pages free_pages
> > used_pages reserved_pages
> > ----------------- --------------- ---------------
> > --------------- ---------------
> > 100.0MB 51200 47693 206
> > 0
> >
> > Which mean that total size is 100 MB
> >
> > But when running the query that should return the same ,
> > we have got different value.
> >
> > The query is :
> >
> > select substring (S.name,1,20) as segment_name,
> > sum (U.size) / (512 / (@@maxpagesize / 2048)) as
> > total_size, sum(curunreservedpgs(db_id(), U.lstart, 0))
> > / (512 / (@@maxpagesize / 2048)) as free_space_MB,
> > 100* ( sum(curunreservedpgs(db_id(), U.lstart, 0)) /
> > (512 / (@@maxpagesize / 2048)) ) / ( sum (U.size) / (512
> > / (@@maxpagesize / 2048)) )
> > as free_space_PCT
> > from master..syssegments S,
> > master..sysusages U,
> > master..sysdevices D
> > where dbid = db_id()
> > and U.vdevno = D.vdevno
> > and vstart between low and high
> > and power (2,S.segment)& U.segmap> 0
> > and substring (S.name,1,20) = 'logsegment'
> > group by S.name
> > order by S.name
> >
> > At DB1 we got total zise 152 MB
> >
> > segment_name total_size free_space_MB
> > free_space_PCT
> > -------------------- ----------- -------------
> > --------------
> > logsegment 152 112
> > 73
> >
> > At DB2 we got 300 MB
> >
> > segment_name total_size free_space_MB
> > free_space_PCT
> > -------------------- ----------- -------------
> > --------------
> > logsegment 300 279
> > 93
> >
> > Why we got different values?


Bret Halford Posted on 2012-07-31 15:51:13.0Z
From: Bret Halford <bret@sybase.com>
User-Agent: Mozilla/5.0 (Windows NT 5.1; rv:14.0) Gecko/20120713 Thunderbird/14.0
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Re: Log segment total size.
References: <5017d831$1@forums-1-dub> <5017e5f1.d4c.1681692777@sybase.com>
In-Reply-To: <5017e5f1.d4c.1681692777@sybase.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <5017fef1$1@forums-1-dub>
Date: 31 Jul 2012 08:51:13 -0700
X-Trace: forums-1-dub 1343749873 172.20.134.152 (31 Jul 2012 08:51:13 -0700)
X-Original-Trace: 31 Jul 2012 08:51:13 -0700, vip152.sybase.com
Lines: 96
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:31291
Article PK: 74176

sp_helpsegment will be in the sybsystemprocs
database. You extract it using the defncopy
utility

defncopy -U<login> -P<password> out sp_helpsegment.txt sybsystemprocs
sp_helpsegment

-bret

On 7/31/2012 8:04 AM, Eyal wrote:
> Thanks.
> This is something I found over the Web.
> I am to green to understand it.
> where can I find the source of sp_helpsegment ?
> The goal is to get the log segment total size.
> It have to be true on any page size and for ASE 12 and 15.
>
>
>> Way too many issues/problems with your query to even try
>> fixing it (eg, why the reference to sysdevices? why the
>> join criteria on vdevno *and* vstart/low/high? why the
>> hardcoded 512's and 2048's?). I'd suggest you take a look
>> at the source code for sp_helpsegment; from this you
>> should be able to come up with a better/correct means of
>> calculating segment space usage.
>>
>> For a simple calculation of total space allocated... add
>> up sysusages.size for the segment and multiply by
>> @@maxpagesize to get total *bytes*; divide by 1024 to get
>> total KBs; divide by 1024*1024 to get total MBs.
>>
>> On 07/31/2012 00:25, Eyal wrote:
>>> The question is regarding the total size of the log
>> segment. >
>>> When running "sp_helpsegment logsegment" we get :
>>> At DB1
>>> total_size total_pages free_pages
>>> used_pages reserved_pages
>>> ----------------- --------------- ---------------
>>> --------------- ---------------
>>> 100.0MB 25600 22156 106
>>> 0
>>> At DB2
>>> total_size total_pages free_pages
>>> used_pages reserved_pages
>>> ----------------- --------------- ---------------
>>> --------------- ---------------
>>> 100.0MB 51200 47693 206
>>> 0
>>>
>>> Which mean that total size is 100 MB
>>>
>>> But when running the query that should return the same ,
>>> we have got different value.
>>>
>>> The query is :
>>>
>>> select substring (S.name,1,20) as segment_name,
>>> sum (U.size) / (512 / (@@maxpagesize / 2048)) as
>>> total_size, sum(curunreservedpgs(db_id(), U.lstart, 0))
>>> / (512 / (@@maxpagesize / 2048)) as free_space_MB,
>>> 100* ( sum(curunreservedpgs(db_id(), U.lstart, 0)) /
>>> (512 / (@@maxpagesize / 2048)) ) / ( sum (U.size) / (512
>>> / (@@maxpagesize / 2048)) )
>>> as free_space_PCT
>>> from master..syssegments S,
>>> master..sysusages U,
>>> master..sysdevices D
>>> where dbid = db_id()
>>> and U.vdevno = D.vdevno
>>> and vstart between low and high
>>> and power (2,S.segment)& U.segmap> 0
>>> and substring (S.name,1,20) = 'logsegment'
>>> group by S.name
>>> order by S.name
>>>
>>> At DB1 we got total zise 152 MB
>>>
>>> segment_name total_size free_space_MB
>>> free_space_PCT
>>> -------------------- ----------- -------------
>>> --------------
>>> logsegment 152 112
>>> 73
>>>
>>> At DB2 we got 300 MB
>>>
>>> segment_name total_size free_space_MB
>>> free_space_PCT
>>> -------------------- ----------- -------------
>>> --------------
>>> logsegment 300 279
>>> 93
>>>
>>> Why we got different values?


Eyal Posted on 2012-08-01 07:41:59.0Z
Sender: d0b.5017e3a0.846930886@sybase.com
From: Eyal
Newsgroups: sybase.public.ase.general
Subject: Re: Log segment total size.
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <5018ddc6.2fb3.1681692777@sybase.com>
References: <5017fef1$1@forums-1-dub>
NNTP-Posting-Host: 172.20.134.41
X-Original-NNTP-Posting-Host: 172.20.134.41
Date: 1 Aug 2012 00:41:59 -0700
X-Trace: forums-1-dub 1343806919 172.20.134.41 (1 Aug 2012 00:41:59 -0700)
X-Original-Trace: 1 Aug 2012 00:41:59 -0700, 172.20.134.41
Lines: 98
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:31302
Article PK: 74190

Thanks

> sp_helpsegment will be in the sybsystemprocs
> database. You extract it using the defncopy
> utility
>
> defncopy -U<login> -P<password> out sp_helpsegment.txt
> sybsystemprocs sp_helpsegment
>
> -bret
>
> On 7/31/2012 8:04 AM, Eyal wrote:
> > Thanks.
> > This is something I found over the Web.
> > I am to green to understand it.
> > where can I find the source of sp_helpsegment ?
> > The goal is to get the log segment total size.
> > It have to be true on any page size and for ASE 12 and
> 15. >
> >
> >> Way too many issues/problems with your query to even
> try >> fixing it (eg, why the reference to sysdevices? why
> the >> join criteria on vdevno *and* vstart/low/high? why
> the >> hardcoded 512's and 2048's?). I'd suggest you take
> a look >> at the source code for sp_helpsegment; from
> this you >> should be able to come up with a
> better/correct means of >> calculating segment space
> usage. >>
> >> For a simple calculation of total space allocated...
> add >> up sysusages.size for the segment and multiply by
> >> @@maxpagesize to get total *bytes*; divide by 1024 to
> get >> total KBs; divide by 1024*1024 to get total MBs.
> >>
> >> On 07/31/2012 00:25, Eyal wrote:
> >>> The question is regarding the total size of the log
> >> segment. >
> >>> When running "sp_helpsegment logsegment" we get :
> >>> At DB1
> >>> total_size total_pages free_pages
> >>> used_pages reserved_pages
> >>> ----------------- --------------- ---------------
> >>> --------------- ---------------
> >>> 100.0MB 25600 22156
> 106 >>> 0
> >>> At DB2
> >>> total_size total_pages free_pages
> >>> used_pages reserved_pages
> >>> ----------------- --------------- ---------------
> >>> --------------- ---------------
> >>> 100.0MB 51200 47693
> 206 >>> 0
> >>>
> >>> Which mean that total size is 100 MB
> >>>
> >>> But when running the query that should return the same
> , >>> we have got different value.
> >>>
> >>> The query is :
> >>>
> >>> select substring (S.name,1,20) as segment_name,
> >>> sum (U.size) / (512 / (@@maxpagesize / 2048)) as
> >>> total_size, sum(curunreservedpgs(db_id(), U.lstart,
> 0)) >>> / (512 / (@@maxpagesize / 2048)) as free_space_MB,
> >>> 100* ( sum(curunreservedpgs(db_id(), U.lstart, 0)) /
> >>> (512 / (@@maxpagesize / 2048)) ) / ( sum (U.size) /
> (512 >>> / (@@maxpagesize / 2048)) )
> >>> as free_space_PCT
> >>> from master..syssegments S,
> >>> master..sysusages U,
> >>> master..sysdevices D
> >>> where dbid = db_id()
> >>> and U.vdevno = D.vdevno
> >>> and vstart between low and high
> >>> and power (2,S.segment)& U.segmap> 0
> >>> and substring (S.name,1,20) = 'logsegment'
> >>> group by S.name
> >>> order by S.name
> >>>
> >>> At DB1 we got total zise 152 MB
> >>>
> >>> segment_name total_size free_space_MB
> >>> free_space_PCT
> >>> -------------------- ----------- -------------
> >>> --------------
> >>> logsegment 152 112
> >>> 73
> >>>
> >>> At DB2 we got 300 MB
> >>>
> >>> segment_name total_size free_space_MB
> >>> free_space_PCT
> >>> -------------------- ----------- -------------
> >>> --------------
> >>> logsegment 300 279
> >>> 93
> >>>
> >>> Why we got different values?
>