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.

db size estimation for new db

4 posts in General Discussion Last posting was on 2010-03-23 05:51:47.0Z
Murali Posted on 2010-03-22 07:27:42.0Z
Reply-To: "Murali" <muraliminchala@yahoo.com>
From: "Murali" <muraliminchala@yahoo.com>
Newsgroups: sybase.public.ase.general
Subject: db size estimation for new db
Lines: 12
Organization: NYKU Systems
MIME-Version: 1.0
Content-Type: text/plain; format=flowed; charset="iso-8859-1"; reply-type=original
Content-Transfer-Encoding: 7bit
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Windows Mail 6.0.6002.18005
X-MimeOLE: Produced By Microsoft MimeOLE V6.0.6002.18005
X-Antivirus: avast! (VPS 100321-1, 21-03-2010), Outbound message
X-Antivirus-Status: Clean
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4ba71bee@forums-1-dub>
Date: 21 Mar 2010 23:27:42 -0800
X-Trace: forums-1-dub 1269242862 10.22.241.152 (21 Mar 2010 23:27:42 -0800)
X-Original-Trace: 21 Mar 2010 23:27:42 -0800, vip152.sybase.com
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:29063
Article PK: 78303

Hi,

Can you please help about database size estimation for new database? what
are the considerations we have to take? how to calculate? which information
we have to gather? like, no. of tables, no. of transactions, no. of
users.......etc

how to calcuate after gathering info?

Thanks in advance.
Murali.Minchala


Bret Halford Posted on 2010-03-22 15:21:14.0Z
From: Bret Halford <bret@sybase.com>
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.9.1.8) Gecko/20100227 Thunderbird/3.0.3
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Re: db size estimation for new db
References: <4ba71bee@forums-1-dub>
In-Reply-To: <4ba71bee@forums-1-dub>
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: <4ba78aea@forums-1-dub>
Date: 22 Mar 2010 07:21:14 -0800
X-Trace: forums-1-dub 1269271274 10.22.241.152 (22 Mar 2010 07:21:14 -0800)
X-Original-Trace: 22 Mar 2010 07:21:14 -0800, vip152.sybase.com
Lines: 30
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:29064
Article PK: 78305


On 3/22/2010 1:27 AM, Murali wrote:
> Hi,
>
> Can you please help about database size estimation for new database?
> what are the considerations we have to take? how to calculate? which
> information we have to gather? like, no. of tables, no. of transactions,
> no. of users.......etc
>
> how to calcuate after gathering info?
>
> Thanks in advance.
> Murali.Minchala

Table definitions, including the first guess at appropriate indexes.
number of rows of initial data in tables
expected growth for each table.

Create the tables and indexes, then run sp_estpace on each table.

The sp_estspace stored procedure will give an estimate
of the space needed for <x> rows in a table.

Sum up the outputs.

The number and size of data-modifying transactions will help determine
the appropriate size of the log. Generally speaking, lots of small
transactions require less log space than few large transactions as
the log can be more frequently dumped/truncated when there aren't larger
transactions.


Cory Sane [TeamSybase] Posted on 2010-03-23 03:36:27.0Z
From: "Cory Sane [TeamSybase]" <cory!=sane>
Newsgroups: sybase.public.ase.general
References: <4ba71bee@forums-1-dub> <4ba78aea@forums-1-dub>
In-Reply-To: <4ba78aea@forums-1-dub>
Subject: Re: db size estimation for new db
Lines: 47
MIME-Version: 1.0
Content-Type: text/plain; format=flowed; charset="iso-8859-1"; reply-type=response
Content-Transfer-Encoding: 7bit
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Windows Mail 6.0.6002.18005
X-MimeOLE: Produced By Microsoft MimeOLE V6.0.6002.18005
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4ba8373b$1@forums-1-dub>
Date: 22 Mar 2010 19:36:27 -0800
X-Trace: forums-1-dub 1269315387 10.22.241.152 (22 Mar 2010 19:36:27 -0800)
X-Original-Trace: 22 Mar 2010 19:36:27 -0800, vip152.sybase.com
X-Authenticated-User: TeamSybase
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:29065
Article PK: 78301

I totally agree with Bret's posting...

FYI, The log needs to be as large as it needs to be ... Duh...
There was once a bad canned answer of 20%... I won a new job because I told a interviewing manager that 20% was bogus. The
right answer is "it depends" on how many transactions are captured between dumping the tranaction log. You will need to
evaluate the rate of changes that your application generates. If you generate 1GB of tranactions between log dumps then you'll
need a tranacton log bigger than one GB. If the application only creates 50MB of tranactions between dumps then you will only
need a small tranaction log. Also remember that the equation is affected by the frequency of the tranaction log dumps... Or, if
you used "trunc log on checkpoint".

HTH
--
Cory Sane
[TeamSybase]
Certified Sybase Associate DBA for ASE 15.0

"Bret Halford" <bret@sybase.com> wrote in message news:4ba78aea@forums-1-dub...
> On 3/22/2010 1:27 AM, Murali wrote:
>> Hi,
>>
>> Can you please help about database size estimation for new database?
>> what are the considerations we have to take? how to calculate? which
>> information we have to gather? like, no. of tables, no. of transactions,
>> no. of users.......etc
>>
>> how to calcuate after gathering info?
>>
>> Thanks in advance.
>> Murali.Minchala
>
>
> Table definitions, including the first guess at appropriate indexes.
> number of rows of initial data in tables
> expected growth for each table.
>
> Create the tables and indexes, then run sp_estpace on each table.
>
> The sp_estspace stored procedure will give an estimate
> of the space needed for <x> rows in a table.
>
> Sum up the outputs.
>
> The number and size of data-modifying transactions will help determine
> the appropriate size of the log. Generally speaking, lots of small transactions require less log space than few large
> transactions as
> the log can be more frequently dumped/truncated when there aren't larger
> transactions.


Michael Peppler [Team Sybase] Posted on 2010-03-23 05:51:47.0Z
From: "Michael Peppler [Team Sybase]" <mpeppler@peppler.org>
Organization: Peppler Consulting SARL
Subject: Re: db size estimation for new db
User-Agent: Pan/0.14.2 (This is not a psychotic episode. It's a cleansing moment of clarity.)
Message-ID: <pan.2010.03.23.05.51.44.292822@peppler.org>
Newsgroups: sybase.public.ase.general
References: <4ba71bee@forums-1-dub> <4ba78aea@forums-1-dub> <4ba8373b$1@forums-1-dub>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 8bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Date: 22 Mar 2010 21:51:47 -0800
X-Trace: forums-1-dub 1269323507 10.22.241.152 (22 Mar 2010 21:51:47 -0800)
X-Original-Trace: 22 Mar 2010 21:51:47 -0800, vip152.sybase.com
Lines: 20
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:29066
Article PK: 78302


On Mon, 22 Mar 2010 19:36:27 -0800, Cory Sane [TeamSybase] wrote:

> I totally agree with Bret's posting...
>
> FYI, The log needs to be as large as it needs to be ... Duh...
> There was once a bad canned answer of 20%... I won a new job because I told a interviewing manager that 20% was bogus. The
> right answer is "it depends" on how many transactions are captured between dumping the tranaction log. You will need to
> evaluate the rate of changes that your application generates. If you generate 1GB of tranactions between log dumps then you'll
> need a tranacton log bigger than one GB. If the application only creates 50MB of tranactions between dumps then you will only
> need a small tranaction log. Also remember that the equation is affected by the frequency of the tranaction log dumps... Or, if
> you used "trunc log on checkpoint".

Indeed.

Also keep in mind that if you use replication then that will also affect
the size of the log as you have to wait for the entire transaction(s) to
get copied to the replication queues before the log can be truncated.

Michael