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.

database size not right

8 posts in General Discussion Last posting was on 2004-04-01 14:34:43.0Z
sen Posted on 2004-03-30 12:59:59.0Z
Sender: 2da0.40696dcf.1804289383@sybase.com
From: Sen
Newsgroups: ianywhere.public.general
Subject: database size not right
X-Mailer: WebNews to Mail Gateway v1.1s
Message-ID: <40696f4f.2da7.846930886@sybase.com>
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 30 Mar 2004 04:59:59 -0800
X-Trace: forums-1-dub 1080651599 10.22.241.41 (30 Mar 2004 04:59:59 -0800)
X-Original-Trace: 30 Mar 2004 04:59:59 -0800, 10.22.241.41
Lines: 13
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:2888
Article PK: 6358

I used to have 5 table in a database 1 table had many row
the size about 90% from the database, when I delete it,
there was some error( I don't remember the message), after I
shutdown the server and run it again, the big table was
gone, but if I see at the exproler the database size not
rights too big for the rest four table.
I tried other operation the database work as usual, is there
any function/method to make the database file size right?
ofcourse I can create new datbase and trasfer exsiting table
to new one, is there any other better solution to fix this?

TIA
Sen


Chris Keating (iAnywhere Solutions) Posted on 2004-03-30 13:41:42.0Z
From: "Chris Keating \(iAnywhere Solutions\)" <FightSpam_keating@iAnywhere.com>
Newsgroups: ianywhere.public.general
References: <40696f4f.2da7.846930886@sybase.com>
Subject: Re: database size not right
Lines: 50
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2800.1158
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1165
X-Original-NNTP-Posting-Host: keating-xp.sybase.com
Message-ID: <40697912$1@forums-2-dub>
X-Original-Trace: 30 Mar 2004 05:41:38 -0800, keating-xp.sybase.com
X-Original-NNTP-Posting-Host: forums-2-dub.sybase.com
X-Original-Trace: 30 Mar 2004 05:41:40 -0800, forums-2-dub.sybase.com
NNTP-Posting-Host: forums-master.sybase.com
X-Original-NNTP-Posting-Host: forums-master.sybase.com
Date: 30 Mar 2004 05:41:42 -0800
X-Trace: forums-1-dub 1080654102 10.22.108.75 (30 Mar 2004 05:41:42 -0800)
X-Original-Trace: 30 Mar 2004 05:41:42 -0800, forums-master.sybase.com
X-Authenticated-User: ngsysop
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:2889
Article PK: 6359

A database file does not shrink in size. When data is deleted, the pages
that become empty are free pages. These free pages are used in subsequent
INSERTs and UPDATEs.

The only way to reduce the file size is by an unload/reload of the database
file.

--

Chris Keating
Sybase Adaptive Server Anywhere Professional Version 8

****************************************************************************
*
Sign up today for your copy of the SQL Anywhere Studio 9 Developer Edition
and try out the market-leading database for mobile, embedded and small to
medium sized business environments for free!

http://www.ianywhere.com/promos/deved/index.html

****************************************************************************
*

iAnywhere Solutions http://www.iAnywhere.com

** Please only post to the newsgroup

** Whitepapers can be found at http://www.iAnywhere.com/developer
** EBFs can be found at http://downloads.sybase.com/swx/sdmain.stm
** Use CaseXpress to report bugs http://casexpress.sybase.com

****************************************************************************
*

<Sen> wrote in message news:40696f4f.2da7.846930886@sybase.com...
> I used to have 5 table in a database 1 table had many row
> the size about 90% from the database, when I delete it,
> there was some error( I don't remember the message), after I
> shutdown the server and run it again, the big table was
> gone, but if I see at the exproler the database size not
> rights too big for the rest four table.
> I tried other operation the database work as usual, is there
> any function/method to make the database file size right?
> ofcourse I can create new datbase and trasfer exsiting table
> to new one, is there any other better solution to fix this?
>
> TIA
> Sen


sen Posted on 2004-03-31 12:35:39.0Z
Sender: 8cf.406abab8.1804289383@sybase.com
From: Sen
Newsgroups: ianywhere.public.general
Subject: Re: database size not right
X-Mailer: WebNews to Mail Gateway v1.1s
Message-ID: <406abb17.8d2.846930886@sybase.com>
References: <40696f4f.2da7.846930886@sybase.com><40697912$1@forums-2-dub>
X-Original-NNTP-Posting-Host: 10.22.241.42
X-Original-Trace: 31 Mar 2004 04:35:35 -0800, 10.22.241.42
Lines: 59
X-Original-NNTP-Posting-Host: forums-2-dub.sybase.com
X-Original-Trace: 31 Mar 2004 04:35:36 -0800, forums-2-dub.sybase.com
NNTP-Posting-Host: forums-master.sybase.com
X-Original-NNTP-Posting-Host: forums-master.sybase.com
Date: 31 Mar 2004 04:35:39 -0800
X-Trace: forums-1-dub 1080736539 10.22.108.75 (31 Mar 2004 04:35:39 -0800)
X-Original-Trace: 31 Mar 2004 04:35:39 -0800, forums-master.sybase.com
X-Authenticated-User: ngsysop
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:2901
Article PK: 6370

So If We have large empty free pages it is better to leave
it or Unload/Reload the databases?
What is the fully benefit if We have large empty pages and
the Contrary?

TIA

> A database file does not shrink in size. When data is
> deleted, the pages that become empty are free pages. These
> free pages are used in subsequent INSERTs and UPDATEs.
>
> The only way to reduce the file size is by an
> unload/reload of the database file.
>
> --
>
> Chris Keating
> Sybase Adaptive Server Anywhere Professional Version 8
>
> **********************************************************
> ****************** *
> Sign up today for your copy of the SQL Anywhere Studio 9
> Developer Edition and try out the market-leading database
> for mobile, embedded and small to medium sized business
> environments for free!
>
> http://www.ianywhere.com/promos/deved/index.html
>
> **********************************************************
> ****************** *
>
> iAnywhere Solutions http://www.iAnywhere.com
>
> ** Please only post to the newsgroup
>
> ** Whitepapers can be found at
> http://www.iAnywhere.com/developer ** EBFs can be found at
> http://downloads.sybase.com/swx/sdmain.stm ** Use
> CaseXpress to report bugs http://casexpress.sybase.com
>
> **********************************************************
> ****************** *
>
> <Sen> wrote in message
> > news:40696f4f.2da7.846930886@sybase.com... I used to
> > have 5 table in a database 1 table had many row the size
> > about 90% from the database, when I delete it, there was
> > some error( I don't remember the message), after I
> > shutdown the server and run it again, the big table was
> > gone, but if I see at the exproler the database size not
> > rights too big for the rest four table. I tried other
> > operation the database work as usual, is there any
> > function/method to make the database file size right?
> ofcourse I can create new datbase and trasfer exsiting
> > table to new one, is there any other better solution to
> fix this? >
> > TIA
> > Sen
>
>


David Fishburn Posted on 2004-03-31 13:49:22.0Z
Newsgroups: ianywhere.public.general
Subject: Re: database size not right
From: David Fishburn <fishburn_spam_off@ianywhere.com>
References: <406abb17.8d2.846930886@sybase.com>
Organization: iAnywhere Solutions
Message-ID: <Xns94BD59BDEE907fishburnsybasecom@192.138.151.106>
User-Agent: Xnews/06.08.25
X-Original-NNTP-Posting-Host: cpe00c049e0d18e-cm012059932637.cpe.net.cable.rogers.com
X-Original-Trace: 31 Mar 2004 05:49:19 -0800, cpe00c049e0d18e-cm012059932637.cpe.net.cable.rogers.com
Lines: 13
X-Original-NNTP-Posting-Host: forums-2-dub.sybase.com
X-Original-Trace: 31 Mar 2004 05:49:20 -0800, forums-2-dub.sybase.com
NNTP-Posting-Host: forums-master.sybase.com
X-Original-NNTP-Posting-Host: forums-master.sybase.com
Date: 31 Mar 2004 05:49:22 -0800
X-Trace: forums-1-dub 1080740962 10.22.108.75 (31 Mar 2004 05:49:22 -0800)
X-Original-Trace: 31 Mar 2004 05:49:22 -0800, forums-master.sybase.com
X-Authenticated-User: ngsysop
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:2904
Article PK: 6371

Sen wrote in news:406abb17.8d2.846930886@sybase.com:

S> So If We have large empty free pages it is better to leave
S> it or Unload/Reload the databases?
S> What is the fully benefit if We have large empty pages and
S> the Contrary?

Yes, the database server will reuse the free pages.
After large deletes it is often good to use the REORGANIZE TABLE
statement, on both the table and the index.

--
David Fishburn


sen Posted on 2004-03-31 12:33:10.0Z
Sender: 8c8.406aba56.1804289383@sybase.com
From: Sen
Newsgroups: ianywhere.public.general
Subject: Re: database size not right
X-Mailer: WebNews to Mail Gateway v1.1s
Message-ID: <406aba82.8cc.846930886@sybase.com>
References: <40696f4f.2da7.846930886@sybase.com>
X-Original-NNTP-Posting-Host: 10.22.241.42
X-Original-Trace: 31 Mar 2004 04:33:06 -0800, 10.22.241.42
Lines: 7
X-Original-NNTP-Posting-Host: forums-2-dub.sybase.com
X-Original-Trace: 31 Mar 2004 04:33:07 -0800, forums-2-dub.sybase.com
NNTP-Posting-Host: forums-master.sybase.com
X-Original-NNTP-Posting-Host: forums-master.sybase.com
Date: 31 Mar 2004 04:33:10 -0800
X-Trace: forums-1-dub 1080736390 10.22.108.75 (31 Mar 2004 04:33:10 -0800)
X-Original-Trace: 31 Mar 2004 04:33:10 -0800, forums-master.sybase.com
X-Authenticated-User: ngsysop
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:2900
Article PK: 6367

Chris thanks,
So If We have large empty free pages it is better to leave
it or Unload/Reload the databases?
What is the fully benefit if We have large empty pages and
the Contrary?

TIA


Raymond Kwong Posted on 2004-04-01 00:00:58.0Z
From: "Raymond Kwong" <raymond.kwong@sybase.com>
Newsgroups: ianywhere.public.general
References: <40696f4f.2da7.846930886@sybase.com> <406aba82.8cc.846930886@sybase.com>
Subject: Re: database size not right
Lines: 23
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2800.1158
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1106
NNTP-Posting-Host: rkwong-pc.sybase.com
X-Original-NNTP-Posting-Host: rkwong-pc.sybase.com
Message-ID: <406b5bba@forums-1-dub>
Date: 31 Mar 2004 16:00:58 -0800
X-Trace: forums-1-dub 1080777658 10.25.100.115 (31 Mar 2004 16:00:58 -0800)
X-Original-Trace: 31 Mar 2004 16:00:58 -0800, rkwong-pc.sybase.com
X-Authenticated-User: techsupp
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:2907
Article PK: 6375

Definitely better to leave it if the entire page is empty.

That would have the same benefit to one preallocating pages in dbspace as
described in our documentation.

Adaptive Server Anywhere Database Administration Guide
8. Working with Database Files
Using additional dbspaces
Pre-allocating space for database files

Saves you the resources need as database negotiates space from file system
for new pages later on.

<Sen> wrote in message news:406aba82.8cc.846930886@sybase.com...
> Chris thanks,
> So If We have large empty free pages it is better to leave
> it or Unload/Reload the databases?
> What is the fully benefit if We have large empty pages and
> the Contrary?
>
> TIA


Greg Fenton Posted on 2004-04-01 02:13:39.0Z
From: Greg Fenton <greg.fenton_NOSPAM_@ianywhere.com>
Organization: iAnywhere Solutions Inc.
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.6) Gecko/20040113 MultiZilla/1.6.2.0c
X-Accept-Language: en-us, en
MIME-Version: 1.0
Newsgroups: ianywhere.public.general
Subject: Re: database size not right
References: <40696f4f.2da7.846930886@sybase.com> <406aba82.8cc.846930886@sybase.com>
In-Reply-To: <406aba82.8cc.846930886@sybase.com>
Content-Type: text/plain; charset=us-ascii; format=flowed
Content-Transfer-Encoding: 7bit
X-Original-NNTP-Posting-Host: cpe000625d8278c-cm.cpe.net.cable.rogers.com
Message-ID: <406b7ad1$1@forums-2-dub>
X-Original-Trace: 31 Mar 2004 18:13:37 -0800, cpe000625d8278c-cm.cpe.net.cable.rogers.com
Lines: 26
X-Original-NNTP-Posting-Host: forums-2-dub.sybase.com
X-Original-Trace: 31 Mar 2004 18:13:38 -0800, forums-2-dub.sybase.com
NNTP-Posting-Host: forums-master.sybase.com
X-Original-NNTP-Posting-Host: forums-master.sybase.com
Date: 31 Mar 2004 18:13:39 -0800
X-Trace: forums-1-dub 1080785619 10.22.108.75 (31 Mar 2004 18:13:39 -0800)
X-Original-Trace: 31 Mar 2004 18:13:39 -0800, forums-master.sybase.com
X-Authenticated-User: ngsysop
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:2909
Article PK: 6377


Sen wrote:
> What is the fully benefit if We have large empty pages and
> the Contrary?

Sen,

You have not indicated anywhere in this thread the version of ASA that
you are using. Please, always post the version and build number with
each post.

Along with what David and Raymond have already posted, if you are using
ASA 8.x or 9.x, then you might take a look at the online docs:

ASA SQL User's Guide
Monitoring and Improving Performance
Fragmentation

Hope this helps,
greg.fenton
--
Greg Fenton
Consultant, Solution Services, iAnywhere Solutions
--------
Visit the iAnywhere Solutions Developer Community
Whitepapers, TechDocs, Downloads
http://www.ianywhere.com/developer/


sen Posted on 2004-04-01 14:34:43.0Z
Sender: 19c4.406c27b2.1804289383@sybase.com
From: Sen
Newsgroups: ianywhere.public.general
Subject: Re: database size not right
X-Mailer: WebNews to Mail Gateway v1.1s
Message-ID: <406c2880.19d8.846930886@sybase.com>
References: <40696f4f.2da7.846930886@sybase.com> <406aba82.8cc.846930886@sybase.com><406b7ad1$1@forums-2-dub>
X-Original-NNTP-Posting-Host: 10.22.241.42
X-Original-Trace: 1 Apr 2004 06:34:40 -0800, 10.22.241.42
Lines: 3
X-Original-NNTP-Posting-Host: forums-2-dub.sybase.com
X-Original-Trace: 1 Apr 2004 06:34:41 -0800, forums-2-dub.sybase.com
NNTP-Posting-Host: forums-master.sybase.com
X-Original-NNTP-Posting-Host: forums-master.sybase.com
Date: 1 Apr 2004 06:34:43 -0800
X-Trace: forums-1-dub 1080830083 10.22.108.75 (1 Apr 2004 06:34:43 -0800)
X-Original-Trace: 1 Apr 2004 06:34:43 -0800, forums-master.sybase.com
X-Authenticated-User: ngsysop
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:2913
Article PK: 6381

Thanks,I am currently using 8.0.0.2213

Sen