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 doubles when adding Column

3 posts in General Discussion Last posting was on 2011-09-27 14:21:13.0Z
Billy Posted on 2011-09-26 18:45:47.0Z
From: Billy <billy>
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 6.1; en-US; rv:1.9.1.5) Gecko/20091204 Thunderbird/3.0
MIME-Version: 1.0
Newsgroups: ianywhere.public.general
Subject: Database Size doubles when adding Column
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: <4e80c85b@forums-1-dub>
Date: 26 Sep 2011 11:45:47 -0700
X-Trace: forums-1-dub 1317062747 10.22.241.152 (26 Sep 2011 11:45:47 -0700)
X-Original-Trace: 26 Sep 2011 11:45:47 -0700, vip152.sybase.com
Lines: 6
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:8153
Article PK: 7167

We have a database that is used for document storage. There are 2
columns that are used for BLOB storage (long binary). When we add a new
column with data type timestamp, it doubles the database size. One of
the test databases we ran it on was originally 2.4GB and increased to 5.1GB.

Any idea as to what would cause this?


John Smirnios [Sybase] Posted on 2011-09-26 21:00:44.0Z
From: "John Smirnios [Sybase]" <smirnios_at_sybase.com>
User-Agent: Mozilla/5.0 (Windows NT 6.1; WOW64; rv:6.0.2) Gecko/20110902 Thunderbird/6.0.2
MIME-Version: 1.0
Newsgroups: ianywhere.public.general
Subject: Re: Database Size doubles when adding Column
References: <4e80c85b@forums-1-dub>
In-Reply-To: <4e80c85b@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: <4e80e7fc$1@forums-1-dub>
Date: 26 Sep 2011 14:00:44 -0700
X-Trace: forums-1-dub 1317070844 10.22.241.152 (26 Sep 2011 14:00:44 -0700)
X-Original-Trace: 26 Sep 2011 14:00:44 -0700, vip152.sybase.com
Lines: 36
X-Authenticated-User: techsupp
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:8154
Article PK: 7171

The first time a page is modified after a checkpoint, a copy of the page
must be saved in the checkpoint log. The server cannot checkpoint while
it is in the middle of adding a column so therefore a copy of every page
modified to perform the ALTER will get saved in the checkpoint log.
Depending on the software version, the checkpoint log will either be
truncated or "right-sized" when the server shuts down.

In your case, I wouldn't expect the blob extension pages to be modified
and, for recent versions at least, I wouldn't even expect the blobs to
be rewritten either. If they are being rewritten, the space occupied by
the old copies of the blobs will be freed later and will be available
for reuse within the dbspace after the next checkpoint.

If there are a couple of GBs for free pages in the dbspace now
(db_property('freepages') I think), the blobs were probably rewritten.
Otherwise, the pages were just written to the checkpoint log and the
file should be trimmed when you shut down.

-john.

John Smirnios
Senior Staff Software Developer
iAnywhere Solutions Engineering

Whitepapers, TechDocs, bug fixes are all available through the iAnywhere
Developer Community at http://www.ianywhere.com/developer

On 9/26/2011 2:45 PM, Billy wrote:
> We have a database that is used for document storage. There are 2
> columns that are used for BLOB storage (long binary). When we add a new
> column with data type timestamp, it doubles the database size. One of
> the test databases we ran it on was originally 2.4GB and increased to
> 5.1GB.
>
> Any idea as to what would cause this?


Billy Posted on 2011-09-27 14:21:13.0Z
From: Billy <billy>
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 6.1; en-US; rv:1.9.1.5) Gecko/20091204 Thunderbird/3.0
MIME-Version: 1.0
Newsgroups: ianywhere.public.general
Subject: Re: Database Size doubles when adding Column
References: <4e80c85b@forums-1-dub> <4e80e7fc$1@forums-1-dub>
In-Reply-To: <4e80e7fc$1@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: <4e81dbd9$1@forums-1-dub>
Date: 27 Sep 2011 07:21:13 -0700
X-Trace: forums-1-dub 1317133273 10.22.241.152 (27 Sep 2011 07:21:13 -0700)
X-Original-Trace: 27 Sep 2011 07:21:13 -0700, vip152.sybase.com
Lines: 46
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:8155
Article PK: 7172

John,

When I got in this morning i noticed the size was a little smaller at
4.7GB. I restarted the database service and the database didn't shrink
in size. I also ran a dbbackup which didn't make any difference. We
are running SQL Anywhere version 10.0.1.4051. I noticed the same
behavior in version 12.0.1.xxxx.

On 9/26/2011 4:00 PM, John Smirnios [Sybase] wrote:
> The first time a page is modified after a checkpoint, a copy of the page
> must be saved in the checkpoint log. The server cannot checkpoint while
> it is in the middle of adding a column so therefore a copy of every page
> modified to perform the ALTER will get saved in the checkpoint log.
> Depending on the software version, the checkpoint log will either be
> truncated or "right-sized" when the server shuts down.
>
> In your case, I wouldn't expect the blob extension pages to be modified
> and, for recent versions at least, I wouldn't even expect the blobs to
> be rewritten either. If they are being rewritten, the space occupied by
> the old copies of the blobs will be freed later and will be available
> for reuse within the dbspace after the next checkpoint.
>
> If there are a couple of GBs for free pages in the dbspace now
> (db_property('freepages') I think), the blobs were probably rewritten.
> Otherwise, the pages were just written to the checkpoint log and the
> file should be trimmed when you shut down.
>
> -john.
>
> John Smirnios
> Senior Staff Software Developer
> iAnywhere Solutions Engineering
>
> Whitepapers, TechDocs, bug fixes are all available through the iAnywhere
> Developer Community at http://www.ianywhere.com/developer
>
>
> On 9/26/2011 2:45 PM, Billy wrote:
>> We have a database that is used for document storage. There are 2
>> columns that are used for BLOB storage (long binary). When we add a new
>> column with data type timestamp, it doubles the database size. One of
>> the test databases we ran it on was originally 2.4GB and increased to
>> 5.1GB.
>>
>> Any idea as to what would cause this?