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.

How to see if a slice for a database is really used

3 posts in General Discussion Last posting was on 2013-01-10 11:54:56.0Z
Simon Posted on 2013-01-08 18:29:55.0Z
Sender: 6329.50ec62b0.1804289383@sybase.com
From: Simon
Newsgroups: sybase.public.ase.general
Subject: How to see if a slice for a database is really used
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <50ec65a3.6407.1681692777@sybase.com>
NNTP-Posting-Host: 172.20.134.41
X-Original-NNTP-Posting-Host: 172.20.134.41
Date: 8 Jan 2013 10:29:55 -0800
X-Trace: forums-1-dub 1357669795 172.20.134.41 (8 Jan 2013 10:29:55 -0800)
X-Original-Trace: 8 Jan 2013 10:29:55 -0800, 172.20.134.41
Lines: 19
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:31637
Article PK: 1158822

- environment information: Sybase 15.7 on AIX

Good evening,

I have a database with data an log perfectly separated. For
ay reason two slices were added with status "data and log
mix"

I'd like to know if there's a way to know whether these two
last slices are being used? if not are there any ways to
remove them?

Other possibility, are there any ways to move their content
on original slices and mark them as not usable?

Thank you


--Simon


Bret Halford Posted on 2013-01-09 20:55:07.0Z
From: Bret Halford <bret.halford@sap.com>
User-Agent: Mozilla/5.0 (Windows NT 6.1; WOW64; rv:17.0) Gecko/20130107 Thunderbird/17.0.2
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Re: How to see if a slice for a database is really used
References: <50ec65a3.6407.1681692777@sybase.com>
In-Reply-To: <50ec65a3.6407.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: <50edd92b$1@forums-1-dub>
Date: 9 Jan 2013 12:55:07 -0800
X-Trace: forums-1-dub 1357764907 172.20.134.152 (9 Jan 2013 12:55:07 -0800)
X-Original-Trace: 9 Jan 2013 12:55:07 -0800, vip152.sybase.com
Lines: 85
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:31638
Article PK: 1158870


On 1/8/2013 11:29 AM, Simon wrote:
> - environment information: Sybase 15.7 on AIX
>
> Good evening,
>
> I have a database with data an log perfectly separated. For
> ay reason two slices were added with status "data and log
> mix"
>
> I'd like to know if there's a way to know whether these two
> last slices are being used? if not are there any ways to
> remove them?
>
> Other possibility, are there any ways to move their content
> on original slices and mark them as not usable?
>
> Thank you
>
>
> --Simon
>

Hi Simon,

I know of two methods offhand.

Here is what I would generally do, this is the old method:

update the sysusages.segmap for these to slices to 0
and then run dbcc dbrepair(dbname, "remap") to cause
that change to be reflected in memory (alternative is to reboot).
Setting the segmap to 0 will prevent future allocations
of extents on these disk fragments.

I would then wait for there to be enough logged activity
on the server to cause any log allocations currently on
these device fragments to fill and be truncated away.

I would then run dbcc usedextents, saving the output to a file

dbcc usedextents(dbname,0,0,0,1)

This prints out a line for each allocated extent in the database,
the output looks like this:

1> dbcc usedextents(test,0,0,0,1)
2> go
OAMPG: 2, extentid: 0, alloc page: 0, objid: 1, indid: 0, ptnid: 1, alloc:
0x1f, dealloc: 0x00, status: 0x00
OAMPG: 0, extentid: 8, alloc page: 0, objid: 1, indid: 3, ptnid: 1, alloc:
0x07, dealloc: 0x00, status: 0x00
OAMPG: 17, extentid: 16, alloc page: 0, objid: 2, indid: 0, ptnid: 2,
alloc:
0xff, dealloc: 0x00, status: 0x00

The "extentid" is the logical page number for the first page of the
extent. Scroll down to the bottom and see if there is any output
that has an extentid value that is equal to or larger than the
sysusages.lstart values for these device fragments.

If so, the "objid" field gives you the object id of the table.
If the table is a DOL user table, you can cause it to migrate
off the device by running REORG REBUILD on it.
If the table is an APL user table, you can cause it to migrate
off the device by running CREATE CLUSTERED INDEX on it.

Once this shows you have no more allocations on the fragments,
and they are the last fragments for the database, you can
directly delete the rows from sysusages and reboot.

Alternatively, there is a newer method. In ASE 15.7 and above,
there is an ALTER DATABASE LOG OFF command.
So you could update the segmap of these fragments to 4 to make
them log-only, run dbcc dbrepair as before. Then use the
new ALTER TABLE LOG OFF command. It will complain if it finds
existing allocations on the fragments.

http://infocenter.sybase.com/help/topic/com.sybase.infocenter.dc31644.1570/html/sag2/CIAEJCFB.htm


--
Bret Halford
Support Architect, ASE Tactical Support Team, AGS Primary Support
Sybase, Inc., an SAP Company
385 Interlocken Crescent, Suite 300, Broomfield, Colorado, 80021


Simon Posted on 2013-01-10 11:54:56.0Z
Sender: 166c.50eeaba0.1804289383@sybase.com
From: Simon
Newsgroups: sybase.public.ase.general
Subject: Re: How to see if a slice for a database is really used
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <50eeac10.169e.1681692777@sybase.com>
References: <50edd92b$1@forums-1-dub>
NNTP-Posting-Host: 172.20.134.41
X-Original-NNTP-Posting-Host: 172.20.134.41
Date: 10 Jan 2013 03:54:56 -0800
X-Trace: forums-1-dub 1357818896 172.20.134.41 (10 Jan 2013 03:54:56 -0800)
X-Original-Trace: 10 Jan 2013 03:54:56 -0800, 172.20.134.41
Lines: 96
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:31643
Article PK: 1158893

Thank you very much Bret,

I'll have a try

Simon

> On 1/8/2013 11:29 AM, Simon wrote:
> > - environment information: Sybase 15.7 on AIX
> >
> > Good evening,
> >
> > I have a database with data an log perfectly separated.
> > For ay reason two slices were added with status "data
> > and log mix"
> >
> > I'd like to know if there's a way to know whether these
> > two last slices are being used? if not are there any
> > ways to remove them?
> >
> > Other possibility, are there any ways to move their
> > content on original slices and mark them as not usable?
> >
> > Thank you
> >
> >
> > --Simon
> >
>
> Hi Simon,
>
> I know of two methods offhand.
>
> Here is what I would generally do, this is the old method:
>
> update the sysusages.segmap for these to slices to 0
> and then run dbcc dbrepair(dbname, "remap") to cause
> that change to be reflected in memory (alternative is to
> reboot). Setting the segmap to 0 will prevent future
> allocations of extents on these disk fragments.
>
> I would then wait for there to be enough logged activity
> on the server to cause any log allocations currently on
> these device fragments to fill and be truncated away.
>
> I would then run dbcc usedextents, saving the output to a
> file
>
> dbcc usedextents(dbname,0,0,0,1)
>
> This prints out a line for each allocated extent in the
> database, the output looks like this:
>
> 1> dbcc usedextents(test,0,0,0,1)
> 2> go
> OAMPG: 2, extentid: 0, alloc page: 0, objid: 1, indid: 0
> , ptnid: 1, alloc: 0x1f, dealloc: 0x00, status: 0x00
> OAMPG: 0, extentid: 8, alloc page: 0, objid: 1, indid: 3
> , ptnid: 1, alloc: 0x07, dealloc: 0x00, status: 0x00
> OAMPG: 17, extentid: 16, alloc page: 0, objid: 2, indid:
> 0, ptnid: 2, alloc:
> 0xff, dealloc: 0x00, status: 0x00
>
> The "extentid" is the logical page number for the first
> page of the extent. Scroll down to the bottom and see if
> there is any output that has an extentid value that is
> equal to or larger than the sysusages.lstart values for
> these device fragments.
>
> If so, the "objid" field gives you the object id of the
> table. If the table is a DOL user table, you can cause it
> to migrate off the device by running REORG REBUILD on it.
> If the table is an APL user table, you can cause it to
> migrate off the device by running CREATE CLUSTERED INDEX
> on it.
>
> Once this shows you have no more allocations on the
> fragments, and they are the last fragments for the
> database, you can directly delete the rows from sysusages
> and reboot.
>
> Alternatively, there is a newer method. In ASE 15.7 and
> above, there is an ALTER DATABASE LOG OFF command.
> So you could update the segmap of these fragments to 4 to
> make them log-only, run dbcc dbrepair as before. Then use
> the new ALTER TABLE LOG OFF command. It will complain if
> it finds existing allocations on the fragments.
>
>
http://infocenter.sybase.com/help/topic/com.sybase.infocenter.dc31644.1570/html/sag2/CIAEJCFB.htm
>
>
> --
> Bret Halford
> Support Architect, ASE Tactical Support Team, AGS Primary
> Support Sybase, Inc., an SAP Company
> 385 Interlocken Crescent, Suite 300, Broomfield, Colorado,
> 80021