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.

Shrink Databases

6 posts in General Discussion Last posting was on 2012-10-24 15:42:40.0Z
RGS Posted on 2012-10-17 14:22:33.0Z
Sender: 4c23.507ebca3.1804289383@sybase.com
From: RGS
Newsgroups: sybase.public.ase.general
Subject: Shrink Databases
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <507ebf29.4cb6.1681692777@sybase.com>
NNTP-Posting-Host: 172.20.134.41
X-Original-NNTP-Posting-Host: 172.20.134.41
Date: 17 Oct 2012 07:22:33 -0700
X-Trace: forums-1-dub 1350483753 172.20.134.41 (17 Oct 2012 07:22:33 -0700)
X-Original-Trace: 17 Oct 2012 07:22:33 -0700, 172.20.134.41
Lines: 20
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:31448
Article PK: 74337

Hi!

We need a software tool for "shrink" a ASE 15 database from
production environment to proof environment

Size of proof environment obviously es smaller than
production environment

We need apply this procedure:

1) Take a backup of database from production environment
2) Load this backup in a temporary server which has the same
size fo production environment
3) Truncate some huge tables of databases in temporary
server
4) "Shrink" database with the tool that we are looking for
5) Load database in proof (smaller) environment


Thanks!!!


Michael Peppler [Team Sybase] Posted on 2012-10-17 15:37:33.0Z
From: "Michael Peppler [Team Sybase]" <mpeppler@peppler.org>
Organization: Peppler Consulting SARL
Subject: Re: Shrink Databases
User-Agent: Pan/0.14.2 (This is not a psychotic episode. It's a cleansing moment of clarity.)
Message-ID: <pan.2012.10.17.15.37.33.349635@peppler.org>
Newsgroups: sybase.public.ase.general
References: <507ebf29.4cb6.1681692777@sybase.com>
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: 17 Oct 2012 08:37:33 -0700
X-Trace: forums-1-dub 1350488253 172.20.134.152 (17 Oct 2012 08:37:33 -0700)
X-Original-Trace: 17 Oct 2012 08:37:33 -0700, vip152.sybase.com
Lines: 26
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:31449
Article PK: 74338


On Wed, 17 Oct 2012 07:22:33 -0700, RGS wrote:

> Hi!
>
> We need a software tool for "shrink" a ASE 15 database from production
> environment to proof environment
>
> Size of proof environment obviously es smaller than production environment
>
> We need apply this procedure:
>
> 1) Take a backup of database from production environment 2) Load this
> backup in a temporary server which has the same size fo production
> environment
> 3) Truncate some huge tables of databases in temporary server
> 4) "Shrink" database with the tool that we are looking for 5) Load
> database in proof (smaller) environment

I'm not aware of any such tool. My best bet would be to set up some form
of bcp job to copy the data for the tables that you require to a new copy
of the database that has the correct size. You could do this with proxy
tables if you didn't want to copy data out to the filesystem.

Michael


Bret Halford Posted on 2012-10-17 17:38:31.0Z
From: Bret Halford <bret.halford@sap.com>
User-Agent: Mozilla/5.0 (Windows NT 6.1; WOW64; rv:15.0) Gecko/20120907 Thunderbird/15.0.1
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Re: Shrink Databases
References: <507ebf29.4cb6.1681692777@sybase.com> <pan.2012.10.17.15.37.33.349635@peppler.org>
In-Reply-To: <pan.2012.10.17.15.37.33.349635@peppler.org>
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: <507eed17$1@forums-1-dub>
Date: 17 Oct 2012 10:38:31 -0700
X-Trace: forums-1-dub 1350495511 172.20.134.152 (17 Oct 2012 10:38:31 -0700)
X-Original-Trace: 17 Oct 2012 10:38:31 -0700, vip152.sybase.com
Lines: 35
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:31450
Article PK: 74341


On 10/17/2012 9:37 AM, Michael Peppler [Team Sybase] wrote:
> On Wed, 17 Oct 2012 07:22:33 -0700, RGS wrote:
>
>> Hi!
>>
>> We need a software tool for "shrink" a ASE 15 database from production
>> environment to proof environment
>>
>> Size of proof environment obviously es smaller than production environment
>>
>> We need apply this procedure:
>>
>> 1) Take a backup of database from production environment 2) Load this
>> backup in a temporary server which has the same size fo production
>> environment
>> 3) Truncate some huge tables of databases in temporary server
>> 4) "Shrink" database with the tool that we are looking for 5) Load
>> database in proof (smaller) environment
>
> I'm not aware of any such tool. My best bet would be to set up some form
> of bcp job to copy the data for the tables that you require to a new copy
> of the database that has the correct size. You could do this with proxy
> tables if you didn't want to copy data out to the filesystem.
>
> Michael

You might be able to use sybmigrate for this (migrate to a smaller
database).

ASE 15.7 has an "alter database log off" command that you could use to
prune log space (but not data) from the database.


RGS Posted on 2012-10-22 18:52:46.0Z
Sender: 47b2.508593bd.1804289383@sybase.com
From: RGS
Newsgroups: sybase.public.ase.general
Subject: Re: Shrink Databases
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <508595fe.4870.1681692777@sybase.com>
References: <507eed17$1@forums-1-dub>
NNTP-Posting-Host: 172.20.134.41
X-Original-NNTP-Posting-Host: 172.20.134.41
Date: 22 Oct 2012 11:52:46 -0700
X-Trace: forums-1-dub 1350931966 172.20.134.41 (22 Oct 2012 11:52:46 -0700)
X-Original-Trace: 22 Oct 2012 11:52:46 -0700, 172.20.134.41
Lines: 56
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:31462
Article PK: 74351

Thank you very much

We reviewed this issue with SYBASE people here in Colombia
but they said us that there are not way to load backup to a
smaller database!! (sybmigrate can not do this)

For us, this is incredible, is obvious that all customers
need migrate a ASE production environment to ASE proof
environment that is smaller in disk capabilities

Think migrate all objets via BCP is very expensive...

For example, Micfrosft SqlServer has a tool that can make
this kind of copies....


Thanks,

> On 10/17/2012 9:37 AM, Michael Peppler [Team Sybase]
> > wrote: On Wed, 17 Oct 2012 07:22:33 -0700, RGS wrote:
> >
> >> Hi!
> >>
> >> We need a software tool for "shrink" a ASE 15 database
> from production >> environment to proof environment
> >>
> >> Size of proof environment obviously es smaller than
> production environment >>
> >> We need apply this procedure:
> >>
> >> 1) Take a backup of database from production
> environment 2) Load this >> backup in a temporary server
> which has the same size fo production >> environment
> >> 3) Truncate some huge tables of databases in temporary
> server >> 4) "Shrink" database with the tool that we are
> looking for 5) Load >> database in proof (smaller)
> environment >
> > I'm not aware of any such tool. My best bet would be to
> > set up some form of bcp job to copy the data for the
> > tables that you require to a new copy of the database
> > that has the correct size. You could do this with proxy
> tables if you didn't want to copy data out to the
> filesystem. >
> > Michael
>
> You might be able to use sybmigrate for this (migrate to a
> smaller database).
>
> ASE 15.7 has an "alter database log off" command that you
> could use to prune log space (but not data) from the
> database.
>
>
>
>


Michael Peppler (Team Sybase) Posted on 2012-10-22 19:09:47.0Z
From: Michael Peppler (Team Sybase) <mpeppler@peppler.org>
Newsgroups: sybase.public.ase.general
Message-ID: <508599fa@forums-1-dub>
References: <507ebf29.4cb6.1681692777@sybase.com> <pan.2012.10.17.15.37.33.349635@peppler.org> <507eed17$1@forums-1-dub> <508595fe.4870.1681692777@sybase.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=iso-8859-1; format=flowed
Content-Transfer-Encoding: 8bit
Subject: Re: Shrink Databases
User-Agent: Unison/2.1.9
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Date: 22 Oct 2012 12:09:47 -0700
X-Trace: forums-1-dub 1350932987 172.20.134.152 (22 Oct 2012 12:09:47 -0700)
X-Original-Trace: 22 Oct 2012 12:09:47 -0700, vip152.sybase.com
Lines: 25
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:31463
Article PK: 74354

On 2012-10-22 18:52:46 +0000, RGS said:

> Thank you very much
>
> We reviewed this issue with SYBASE people here in Colombia
> but they said us that there are not way to load backup to a
> smaller database!! (sybmigrate can not do this)
>
> For us, this is incredible, is obvious that all customers
> need migrate a ASE production environment to ASE proof
> environment that is smaller in disk capabilities

Well, I undestand your frustration, but not everyone does it that way.
Where I have worked (over the last 20 years or so) we have usually had
copies of prod data that was similar in size and data content (though
with sensitive data removed or masked). This has the advantage of
giving you an environment where you can do full scale load tests, and
ensure that your queries will in fact perform as expected once you hit
the prod environment.

Michael


Shrink Databases Posted on 2012-10-24 15:42:40.0Z
Sender: 7fe5.5087ffd4.1804289383@sybase.com
From: Shrink Databases
Newsgroups: sybase.public.ase.general
Subject: Re: Shrink Databases
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <50880c70.3d0.1681692777@sybase.com>
References: <507ebf29.4cb6.1681692777@sybase.com>
NNTP-Posting-Host: 172.20.134.41
X-Original-NNTP-Posting-Host: 172.20.134.41
Date: 24 Oct 2012 08:42:40 -0700
X-Trace: forums-1-dub 1351093360 172.20.134.41 (24 Oct 2012 08:42:40 -0700)
X-Original-Trace: 24 Oct 2012 08:42:40 -0700, 172.20.134.41
Lines: 16
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:31472
Article PK: 74361

4) "Shrink" database with the tool that we are looking for

- Make sure you have logical database backup.
- Create a smaller database, run your scripts from logical
backup to create the required schema.
- Now, you can select into data as needed a smaller subset
of rows from original database (turn on trunc log on chkpt
option).

Things you have to figure out: how to take logical backup
and the table constraint dependency before moving the data.

Once you have this information, you can start pumping out
shink-dbs as many as you want.

sorry no MS click and done approach...