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.

Wanted: two ASE improvements that *really* improve uptime

4 posts in Product Futures Discussion Last posting was on 2002-03-09 23:04:38.0Z
Rob Verschoor Posted on 2002-01-15 14:09:33.0Z
Reply-To: "Rob Verschoor" <rob@sypron.nl>
From: "Rob Verschoor" <rob@sypron.nl>
Subject: Wanted: two ASE improvements that *really* improve uptime
Date: Tue, 15 Jan 2002 15:09:33 +0100
Lines: 58
Organization: Sypron B.V.
MIME-Version: 1.0
Content-Type: text/plain; charset="iso-8859-1"
Content-Transfer-Encoding: 7bit
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 5.00.2919.6600
X-MimeOLE: Produced By Microsoft MimeOLE V5.00.2919.6600
Message-ID: <pGAua7cnBHA.266@forums.sybase.com>
Newsgroups: sybase.public.ase.product_futures_discussion
NNTP-Posting-Host: p0723.spl.euronet.nl 194.134.104.211
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com
Xref: forums-1-dub sybase.public.ase.product_futures_discussion:929
Article PK: 95166

There's many things I would like to see in ASE, but two things I would
really like -- mainly because I keep seeing these things again and
again in practice, and invariably cause operational problems because
of the long downtime they require.

First, I'd like to see 'reorg' being enhanced so that it does real
defragmentation of data pages, and therefore of tables -- 'real'
meaning that packs together various pages with only a few rows on them
and deallocates the pages that are thus becoming unused. Currently
reorg only cleans up individual pages (only rearranging rows on the
page itself), and you can end up with a very large table consisting
mostly of pages with a single row. In practice, I've often seen this
happening -- I recall a case of a 50Gb table where some 50% could be
reclaimed if only the table could be rebuilt completely; problem is
that this requires significant application downtime, which is not
available (doing a reorg rebuild won't help either, because you don't
usually have tens of gigabytes of free space availabe to do this --
which leaves you with BCP-out, BCP-in).
The sort of thing I'd want is like the 'reorg rebuild table_name
index_name', which does an extent at a time and therefore can run
while other processing is taking place. Making this functionality
available for data pages would be a significant improvement of the
'continous availability' concept that has been promoted by Sybase.

The second thing is an on-line command/tool/utility to fix individual
corruptions as found by dbcc checkstorage. Currently, dbcc
checkstorage is indeed a much better way of detecting corruptions than
the 'classic' dbcc commands such as checkalloc etc., but when any
corruptions are found, you still need dbcc tablealloc/indexalloc to
fix these corruptions. For large tables, this will require the tables
to be locked and therefore this means application downtime. Because
the chance of corruptions increases with the size of a table, these
problems always occur on the largest tables, which tend to be the most
important ones for your applications.
In fact, there is an error message in dbcc checkstorage/checkverify
mentioning that you should run 'dbcc checkrepair' -- which doesn't
exist. I understand the original idea was that dbcc checkrepair would
indeed go and fix individual corruptions, but it was never
implemented. Adding feature in the next ASE version would make life so
much better for DBAs !

My 2 eurocents,

Rob
----------------------------------------------------------------------
Rob Verschoor

Certified Sybase Professional DBA for ASE 12.0/11.5/11.0

Author of "The Complete Sybase ASE Quick Reference Guide"
Online orders accepted at http://www.sypron.nl/qr

email mailto:rob@sypron.nl.*No*Spam*Please*
WWW http://www.sypron.nl
snail Sypron B.V., P.O.Box 10695, 2501HR Den Haag, The Netherlands
----------------------------------------------------------------------


Tricia Posted on 2002-03-09 23:04:38.0Z
From: "Tricia" <tricia.crighton@ntlworld.com>
References: <pGAua7cnBHA.266@forums.sybase.com>
Subject: Re: Wanted: two ASE improvements that *really* improve uptime
Date: Sat, 9 Mar 2002 23:04:38 -0000
Lines: 71
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2600.0000
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2600.0000
Message-ID: <iZ4UaJ8xBHA.318@forums.sybase.com>
Newsgroups: sybase.public.ase.product_futures_discussion
NNTP-Posting-Host: m61-mp1-cvx3c.nth.ntl.com 62.255.196.61
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com
Xref: forums-1-dub sybase.public.ase.product_futures_discussion:707
Article PK: 94235

You've got my vote on these two!
Managing/fixing Gigabytes of Data is pretty difficult, just the amount
of time it takes to BCP out a huge table. I like the idea of the
on-the-fly
defrag.

Regards,
Tricia.

"Rob Verschoor" <rob@sypron.nl> wrote in message
news:pGAua7cnBHA.266@forums.sybase.com...
> There's many things I would like to see in ASE, but two things I would
> really like -- mainly because I keep seeing these things again and
> again in practice, and invariably cause operational problems because
> of the long downtime they require.
>
> First, I'd like to see 'reorg' being enhanced so that it does real
> defragmentation of data pages, and therefore of tables -- 'real'
> meaning that packs together various pages with only a few rows on them
> and deallocates the pages that are thus becoming unused. Currently
> reorg only cleans up individual pages (only rearranging rows on the
> page itself), and you can end up with a very large table consisting
> mostly of pages with a single row. In practice, I've often seen this
> happening -- I recall a case of a 50Gb table where some 50% could be
> reclaimed if only the table could be rebuilt completely; problem is
> that this requires significant application downtime, which is not
> available (doing a reorg rebuild won't help either, because you don't
> usually have tens of gigabytes of free space availabe to do this --
> which leaves you with BCP-out, BCP-in).
> The sort of thing I'd want is like the 'reorg rebuild table_name
> index_name', which does an extent at a time and therefore can run
> while other processing is taking place. Making this functionality
> available for data pages would be a significant improvement of the
> 'continous availability' concept that has been promoted by Sybase.
>
> The second thing is an on-line command/tool/utility to fix individual
> corruptions as found by dbcc checkstorage. Currently, dbcc
> checkstorage is indeed a much better way of detecting corruptions than
> the 'classic' dbcc commands such as checkalloc etc., but when any
> corruptions are found, you still need dbcc tablealloc/indexalloc to
> fix these corruptions. For large tables, this will require the tables
> to be locked and therefore this means application downtime. Because
> the chance of corruptions increases with the size of a table, these
> problems always occur on the largest tables, which tend to be the most
> important ones for your applications.
> In fact, there is an error message in dbcc checkstorage/checkverify
> mentioning that you should run 'dbcc checkrepair' -- which doesn't
> exist. I understand the original idea was that dbcc checkrepair would
> indeed go and fix individual corruptions, but it was never
> implemented. Adding feature in the next ASE version would make life so
> much better for DBAs !
>
> My 2 eurocents,
>
> Rob
> ----------------------------------------------------------------------
> Rob Verschoor
>
> Certified Sybase Professional DBA for ASE 12.0/11.5/11.0
>
> Author of "The Complete Sybase ASE Quick Reference Guide"
> Online orders accepted at http://www.sypron.nl/qr
>
> email mailto:rob@sypron.nl.*No*Spam*Please*
> WWW http://www.sypron.nl
> snail Sypron B.V., P.O.Box 10695, 2501HR Den Haag, The Netherlands
> ----------------------------------------------------------------------
>
>


Sethu Posted on 2002-03-05 02:53:12.0Z
Message-ID: <3C843318.2DADBAFE@sybase.com>
Date: Mon, 04 Mar 2002 21:53:12 -0500
From: Sethu <sethu@sybase.com>
Organization: Sybase, Inc.
X-Mailer: Mozilla 4.79 [en] (Windows NT 5.0; U)
X-Accept-Language: en
MIME-Version: 1.0
To: Rob Verschoor <rob@sypron.nl>
Subject: Re: Wanted: two ASE improvements that *really* improve uptime
References: <pGAua7cnBHA.266@forums.sybase.com>
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
Newsgroups: sybase.public.ase.product_futures_discussion
Lines: 82
NNTP-Posting-Host: 10.22.91.118
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com
Xref: forums-1-dub sybase.public.ase.product_futures_discussion:748
Article PK: 94275

Hi Rob,

I'm investigating the issues surround reorg command.
This is a big grief for our customers and being looked into.

thanks,
Sethu

Rob Verschoor wrote:
>
> There's many things I would like to see in ASE, but two things I would
> really like -- mainly because I keep seeing these things again and
> again in practice, and invariably cause operational problems because
> of the long downtime they require.
>
> First, I'd like to see 'reorg' being enhanced so that it does real
> defragmentation of data pages, and therefore of tables -- 'real'
> meaning that packs together various pages with only a few rows on them
> and deallocates the pages that are thus becoming unused. Currently
> reorg only cleans up individual pages (only rearranging rows on the
> page itself), and you can end up with a very large table consisting
> mostly of pages with a single row. In practice, I've often seen this
> happening -- I recall a case of a 50Gb table where some 50% could be
> reclaimed if only the table could be rebuilt completely; problem is
> that this requires significant application downtime, which is not
> available (doing a reorg rebuild won't help either, because you don't
> usually have tens of gigabytes of free space availabe to do this --
> which leaves you with BCP-out, BCP-in).
> The sort of thing I'd want is like the 'reorg rebuild table_name
> index_name', which does an extent at a time and therefore can run
> while other processing is taking place. Making this functionality
> available for data pages would be a significant improvement of the
> 'continous availability' concept that has been promoted by Sybase.
>
> The second thing is an on-line command/tool/utility to fix individual
> corruptions as found by dbcc checkstorage. Currently, dbcc
> checkstorage is indeed a much better way of detecting corruptions than
> the 'classic' dbcc commands such as checkalloc etc., but when any
> corruptions are found, you still need dbcc tablealloc/indexalloc to
> fix these corruptions. For large tables, this will require the tables
> to be locked and therefore this means application downtime. Because
> the chance of corruptions increases with the size of a table, these
> problems always occur on the largest tables, which tend to be the most
> important ones for your applications.
> In fact, there is an error message in dbcc checkstorage/checkverify
> mentioning that you should run 'dbcc checkrepair' -- which doesn't
> exist. I understand the original idea was that dbcc checkrepair would
> indeed go and fix individual corruptions, but it was never
> implemented. Adding feature in the next ASE version would make life so
> much better for DBAs !
>
> My 2 eurocents,
>
> Rob
> ----------------------------------------------------------------------
> Rob Verschoor
>
> Certified Sybase Professional DBA for ASE 12.0/11.5/11.0
>
> Author of "The Complete Sybase ASE Quick Reference Guide"
> Online orders accepted at http://www.sypron.nl/qr
>
> email mailto:rob@sypron.nl.*No*Spam*Please*
> WWW http://www.sypron.nl
> snail Sypron B.V., P.O.Box 10695, 2501HR Den Haag, The Netherlands
> ----------------------------------------------------------------------


Denny_Chen Posted on 2002-01-17 02:00:08.0Z
From: Denny_Chen@capgroup.com
Subject: Re: Wanted: two ASE improvements that *really* improve uptime
MIME-Version: 1.0
References: <pGAua7cnBHA.266@forums.sybase.com>
X-Newsreader: Lotus Notes Release 5.0.8 June 18, 2001
X-MIMETrack: Serialize by Notes Client on Denny Chen/CDS/CG/CAPITAL(Release 5.0.8 |June 18, 2001) at 01/16/2002 05:55:00 PM, Serialize complete at 01/16/2002 05:55:00 PM
Content-Type: text/plain; charset="us-ascii"
Message-ID: <wJBgwqvnBHA.337@forums.sybase.com>
Newsgroups: sybase.public.ase.product_futures_discussion
Date: Wed, 16 Jan 2002 21:00:08 -0500
Lines: 22
NNTP-Posting-Host: host020.bro.capgroup.com 148.107.10.20
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com
Xref: forums-1-dub sybase.public.ase.product_futures_discussion:922
Article PK: 94457

Hi,

I like to follow up with Rob's suggestion on dbcc checkstorage.

BMC used to have a product called DB-Verifier which works with
SQL-BackTrack. What these 2 tools provide is dbcc with backup. Now that
BMC has discontinued this product, perhaps Sybase can pick up this tool at
a good price and enhance it.



I've been running some tests with ASE 12.0 backupserver, and I can see
that the changes made for ASE 12.0 backupserver have increased backup
throughput (mb/sec). Good work guys.

Along with the new compress dump support in ASE 12.5, how about a
configuration file for backupserver, just like dataserver, so that
configuration parameters such as compression level can be set in a file
rather than needing to change scripts. This goes along with designing
features that's easy to use and without less user impact.

Regards,
Denny