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.

Rebuild into a new database!

7 posts in General Discussion Last posting was on 2005-08-17 14:34:52.0Z
Oscar LM Posted on 2005-08-15 17:12:31.0Z
Sender: c6b.4300cab0.1804289383@sybase.com
From: Oscar LM
Newsgroups: ianywhere.public.general
Subject: Rebuild into a new database!
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4300ccff.c8b.1681692777@sybase.com>
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 15 Aug 2005 10:12:31 -0700
X-Trace: forums-1-dub 1124125951 10.22.241.41 (15 Aug 2005 10:12:31 -0700)
X-Original-Trace: 15 Aug 2005 10:12:31 -0700, 10.22.241.41
Lines: 13
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:4687
Article PK: 8484

I am currently using Sybase - ASA 7.02, and the database
size is
2.5 GB and the log file is 3.6 GB.
We are trying to rebuild the database and it is taking more
than 65 hours to rebuild.

Is there anything I can do to shorten the time that it takes
to rebuild? We have overseas customers and we can't afford
to have the database down for such a long time.

Thanks
Oscar
olm@parasoldev.com


Breck Carter [TeamSybase] Posted on 2005-08-15 17:21:13.0Z
From: "Breck Carter [TeamSybase]" <NOSPAM__bcarter@risingroad.com>
Newsgroups: ianywhere.public.general
Subject: Re: Rebuild into a new database!
Organization: RisingRoad Professional Services
Reply-To: NOSPAM__bcarter@risingroad.com
Message-ID: <sgj1g11l35krndi8srfmpku2kh963j0uho@4ax.com>
References: <4300ccff.c8b.1681692777@sybase.com>
X-Newsreader: Forte Agent 2.0/32.640
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: 216.201.157.252
X-Original-NNTP-Posting-Host: 216.201.157.252
Date: 15 Aug 2005 10:21:13 -0700
X-Trace: forums-1-dub 1124126473 216.201.157.252 (15 Aug 2005 10:21:13 -0700)
X-Original-Trace: 15 Aug 2005 10:21:13 -0700, 216.201.157.252
Lines: 34
X-Authenticated-User: TeamSybase
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:4688
Article PK: 8485

It should only take a few minutes on a modern computer.

Make sure your hard drive is defragmented, and that there is *plenty*
of space for a new .DB file.

Use separate dbunload, dbinit and dbisql steps to unload and reload;
do not use Sybase Central or any of the dbunload -ar/-ac/-ar options.
This way you have more control over the process, and will have a
better idea where the problem is.

Breck

On 15 Aug 2005 10:12:31 -0700, Oscar LM wrote:

>I am currently using Sybase - ASA 7.02, and the database
>size is
>2.5 GB and the log file is 3.6 GB.
>We are trying to rebuild the database and it is taking more
>than 65 hours to rebuild.
>
>Is there anything I can do to shorten the time that it takes
>to rebuild? We have overseas customers and we can't afford
>to have the database down for such a long time.
>
>Thanks
>Oscar
>olm@parasoldev.com

--
SQL Anywhere Studio 9 Developer's Guide
Buy the book: http://www.amazon.com/exec/obidos/ASIN/1556225067/risingroad-20
bcarter@risingroad.com
RisingRoad SQL Anywhere and MobiLink Professional Services
www.risingroad.com


Greg Fenton Posted on 2005-08-15 17:22:42.0Z
From: Greg Fenton <greg.fenton_NOSPAM_@ianywhere.com>
Organization: iAnywhere Solutions Inc.
User-Agent: Mozilla Thunderbird 1.6.3.2f (Windows/20050317)
X-Accept-Language: en-us, en
MIME-Version: 1.0
Newsgroups: ianywhere.public.general
Subject: Re: Rebuild into a new database!
References: <4300ccff.c8b.1681692777@sybase.com>
In-Reply-To: <4300ccff.c8b.1681692777@sybase.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: 10.25.98.144
X-Original-NNTP-Posting-Host: 10.25.98.144
Message-ID: <4300cf62$1@forums-1-dub>
Date: 15 Aug 2005 10:22:42 -0700
X-Trace: forums-1-dub 1124126562 10.25.98.144 (15 Aug 2005 10:22:42 -0700)
X-Original-Trace: 15 Aug 2005 10:22:42 -0700, 10.25.98.144
Lines: 35
X-Authenticated-User: techsupp
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:4689
Article PK: 8486


Oscar LM wrote:
> I am currently using Sybase - ASA 7.02, and the database
> size is 2.5 GB and the log file is 3.6 GB.
> We are trying to rebuild the database and it is taking more
> than 65 hours to rebuild.
>

65 hours is an awful long period of time for only 2.5GB of disk space,
but depending on the design of your schema and/or the hardware it is
running on, you might be forcing the server or the OS to do more work
than it should have to.

How are you doing the rebuild?
Do you have any calculated columns?
Do you have lots of triggers?

There are lots of things you can consider:

- hard drive fragmentation (i.e. defrag before unload/reload)
- page size of new database file (should be 4k for most applications)
- pre-grow the new db file (e.g. to 2.5 GB) prior to reload (use ALTER
DBSPACE)
- use an internal unload/internal reload

BTW: is there a particular reason that you have decided to rebuild your
database?

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


Oscar LM. Posted on 2005-08-15 21:15:37.0Z
Sender: 198a.430101a6.846930886@sybase.com
From: Oscar LM.
Newsgroups: ianywhere.public.general
Subject: Re: Rebuild into a new database!
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <430105f5.19e3.1681692777@sybase.com>
References: <4300cf62$1@forums-1-dub>
X-Original-NNTP-Posting-Host: 10.22.241.42
X-Original-Trace: 15 Aug 2005 14:15:33 -0700, 10.22.241.42
Lines: 51
X-Original-NNTP-Posting-Host: forums-2-dub.sybase.com
X-Original-Trace: 15 Aug 2005 14:15:34 -0700, forums-2-dub.sybase.com
NNTP-Posting-Host: forums-master.sybase.com
X-Original-NNTP-Posting-Host: forums-master.sybase.com
Date: 15 Aug 2005 14:15:37 -0700
X-Trace: forums-1-dub 1124140537 10.22.108.75 (15 Aug 2005 14:15:37 -0700)
X-Original-Trace: 15 Aug 2005 14:15:37 -0700, forums-master.sybase.com
X-Authenticated-User: ngsysop
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:4691
Article PK: 8488


> Oscar LM wrote:
> > I am currently using Sybase - ASA 7.02, and the database
> > size is 2.5 GB and the log file is 3.6 GB.
> > We are trying to rebuild the database and it is taking
> > more than 65 hours to rebuild.
> >
>
> 65 hours is an awful long period of time for only 2.5GB of
> disk space, but depending on the design of your schema
> and/or the hardware it is running on, you might be
> forcing the server or the OS to do more work than it
> should have to.
>
> How are you doing the rebuild?
> Do you have any calculated columns?
> Do you have lots of triggers?
>
> There are lots of things you can consider:
>
> - hard drive fragmentation (i.e. defrag before
> unload/reload) - page size of new database file (should be
> 4k for most applications) - pre-grow the new db file (e.g.
> to 2.5 GB) prior to reload (use ALTER DBSPACE)
> - use an internal unload/internal reload
>
> BTW: is there a particular reason that you have decided to
> rebuild your database?
>
> greg.fenton
> --
> Greg Fenton
> Consultant, Solution Services, iAnywhere Solutions
> --------
> Visit the iAnywhere Solutions Developer Community
> Whitepapers, TechDocs, Downloads
> http://www.ianywhere.com/developer/

The only reason is that the log file keeps getting bigger.
We would like to keep it small for performance issues.

I know that renaming the log file will help, but we normally
like to rebuild everytime we are going through this
exercise!

PS. The database is running on a 4 processor DELL server,
Windows 2003 with 4GB of RAM and 3.5Ghz and plenty of hard
drive space.

Thanks
Oscar.


Chris Keating (iAnywhere Solutions) Posted on 2005-08-16 02:54:33.0Z
From: "Chris Keating \(iAnywhere Solutions\)" <Spam_NoThanks_keating@iAnywhere.com>
Newsgroups: ianywhere.public.general
References: <4300cf62$1@forums-1-dub> <430105f5.19e3.1681692777@sybase.com>
Subject: Re: Rebuild into a new database!
Lines: 17
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.2180
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2180
X-RFC2646: Format=Flowed; Original
NNTP-Posting-Host: keating-xp2.sybase.com
X-Original-NNTP-Posting-Host: keating-xp2.sybase.com
Message-ID: <43015569$1@forums-1-dub>
Date: 15 Aug 2005 19:54:33 -0700
X-Trace: forums-1-dub 1124160873 10.25.98.199 (15 Aug 2005 19:54:33 -0700)
X-Original-Trace: 15 Aug 2005 19:54:33 -0700, keating-xp2.sybase.com
X-Authenticated-User: techsupp
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:4692
Article PK: 17377


> The only reason is that the log file keeps getting bigger.
> We would like to keep it small for performance issues.
>
> I know that renaming the log file will help, but we normally
> like to rebuild everytime we are going through this
> exercise!

That is sort of like rebuilding an engine every time you want to change the
oil.

Have you considered using your backup as a mechanism to control the log file
size? As part of you backup, truncate the log. This will allow you to
control the transaction log file as part of your routine maintenance for
this database.


Breck Carter [TeamSybase] Posted on 2005-08-16 10:28:13.0Z
From: "Breck Carter [TeamSybase]" <NOSPAM__bcarter@risingroad.com>
Newsgroups: ianywhere.public.general
Subject: Re: Rebuild into a new database!
Organization: RisingRoad Professional Services
Reply-To: NOSPAM__bcarter@risingroad.com
Message-ID: <qrf3g116cu3neiu7j6e3uh4uumuov57uv8@4ax.com>
References: <4300cf62$1@forums-1-dub> <430105f5.19e3.1681692777@sybase.com> <43015569$1@forums-1-dub>
X-Newsreader: Forte Agent 2.0/32.640
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: 63.97.252.227
X-Original-NNTP-Posting-Host: 63.97.252.227
Date: 16 Aug 2005 03:28:13 -0700
X-Trace: forums-1-dub 1124188093 63.97.252.227 (16 Aug 2005 03:28:13 -0700)
X-Original-Trace: 16 Aug 2005 03:28:13 -0700, 63.97.252.227
Lines: 15
X-Authenticated-User: TeamSybase
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:4693
Article PK: 8490

On 15 Aug 2005 19:54:33 -0700, "Chris Keating \(iAnywhere Solutions\)"

<Spam_NoThanks_keating@iAnywhere.com> wrote:

>rebuilding an engine every time you want to change the oil

That reminds me of my Triumph TR6, many years ago :)

Breck

--
SQL Anywhere Studio 9 Developer's Guide
Buy the book: http://www.amazon.com/exec/obidos/ASIN/1556225067/risingroad-20
bcarter@risingroad.com
RisingRoad SQL Anywhere and MobiLink Professional Services
www.risingroad.com


Chris Keating (iAnywhere Solutions) Posted on 2005-08-17 14:34:52.0Z
From: "Chris Keating \(iAnywhere Solutions\)" <Spam_NoThanks_keating@iAnywhere.com>
Newsgroups: ianywhere.public.general
References: <4300cf62$1@forums-1-dub> <430105f5.19e3.1681692777@sybase.com> <43015569$1@forums-1-dub> <qrf3g116cu3neiu7j6e3uh4uumuov57uv8@4ax.com>
Subject: Re: Rebuild into a new database!
Lines: 22
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.2180
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2180
X-RFC2646: Format=Flowed; Original
NNTP-Posting-Host: keating-xp2.sybase.com
X-Original-NNTP-Posting-Host: keating-xp2.sybase.com
Message-ID: <43034b0c$1@forums-1-dub>
Date: 17 Aug 2005 07:34:52 -0700
X-Trace: forums-1-dub 1124289292 10.25.98.199 (17 Aug 2005 07:34:52 -0700)
X-Original-Trace: 17 Aug 2005 07:34:52 -0700, keating-xp2.sybase.com
X-Authenticated-User: techsupp
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:4698
Article PK: 17373

There are exceptions to every rule :-)

"Breck Carter [TeamSybase]" <NOSPAM__bcarter@risingroad.com> wrote in
message news:qrf3g116cu3neiu7j6e3uh4uumuov57uv8@4ax.com...
> On 15 Aug 2005 19:54:33 -0700, "Chris Keating \(iAnywhere Solutions\)"
> <Spam_NoThanks_keating@iAnywhere.com> wrote:
>
>>rebuilding an engine every time you want to change the oil
>
> That reminds me of my Triumph TR6, many years ago :)
>
> Breck
>
> --
> SQL Anywhere Studio 9 Developer's Guide
> Buy the book:
> http://www.amazon.com/exec/obidos/ASIN/1556225067/risingroad-20
> bcarter@risingroad.com
> RisingRoad SQL Anywhere and MobiLink Professional Services
> www.risingroad.com