cancel
Showing results for 
Search instead for 
Did you mean: 

Maximum DB size for SAP on ASE?

Former Member
0 Kudos

Hello ASE experts,

If I'm not wrong, a ASE database can be up to 32TB when a pagesize of 16KB is used - which is the case for the SAP BusinessSuite.

Nevertheless, this seems to be a pretty hard limit, which does not exist for the other available databases like Oracle or SQL Server.

Am I correct? Any other comments?

Thanks,

Uli

Accepted Solutions (1)

Accepted Solutions (1)

JanStallkamp
Employee
Employee
0 Kudos

Hello Uli.

Yes, there is a limit in ASE for 32TB in the current version, but I expect changes to this limit.

Please also take into account that this is the size of compressed data. Data compression is part of the standard ASE license for SAP customers. If you calculate with a 50% compression rate (rough guess, that really depends on the type of data in the system) we are talking about 64TB data in the system.

Regards,

Jan

HuseyinBilgen
Active Contributor
0 Kudos

Hi Jan,

Can you give us a tip on how to compress Sybase ASE DB running under SAP Systems?

Recently (yesterday ) I migrated one of our systems running on Windows/SQL to Windows/ASE and database size increased from 55GB to 100GB.

SQL2008R2 has default ROW, PAGE and UC compressions, which are also used by R3load in default.

But, I didn't see the same behaviour for R3load for ASE (I used latest kernel release).

So I think I have to manually compress the DB by some way.

For SQL Server, there is a report called MSSCOMPRESS within ABAP and you can compress the database with this report.

My question;

WHat are the alternative ways to compress the Sybase ASE for SAP?

Regards

JanStallkamp
Employee
Employee
0 Kudos

Hi Huseyin.

First some remark as a moderator: Please consider opening a new thread when asking questions. Currently we don't have much traffic in this SCN space but in the future that might help people finding information here.

To your question:

How have you migrated the system? If the ASE database is created by SAPInst the compression should be enabled automatically. There are some tables where compression is disabled explicitly (e.g. VBDATA) but everything else should be compressed. Currently there is no report for compression available as there should be no SAP system running on ASE without compression. You can check if compression is enabled for a table in the DBA Cockpit (assuming that the Cockpit is set up correctly, see the InstGuide and the DBA Guide for details):

- start transaction DBACOCKPIT

- click on the tab for the local database (or select the remote DB and the go to the tab)

- chose Space->Tables and Indexes->Single Table Analysis

- select a table that should be compressed (e.g. SFLIGHT)

- On the 'Summary' tab you should see a 'Compression' group showing data compression level 'page' and a LOB compression level of 100.

In general I would say that there might be differences in DB size between a compressed MS SQL Server and ASE depending on the type of data but such a difference looks strange to me. Is that 100GB really the size of data inside the DB or is this the size of the SID-database including all the unreserved space of that DB? What is DBA Cockpit showing in Space->Databases in the columns 'Database Size' and 'Unreserved Space'?

Regards,

Jan

HuseyinBilgen
Active Contributor
0 Kudos

Hi Jan,

Here I'm attaching a table space overview and DB size.

Tables seems to be compressed at Page level as you mentioned. But the Used DB size is 100G as you can see.

DB

Table DDNTF

JanStallkamp
Employee
Employee
0 Kudos

Hi Huseyin.

The information from the Cockpit looks like everything is set up correctly. There are two things that could be of interest here. If you have been using older versions of DBSL, ODBC driver, R3load... for the migration this could have been impacting compression. The second szenario I can imagine is that you are looking at a system with a lot of LOB-data where the LOBs are slightly larger than the off-row-limit. As this limit might be changed it would be interesting to have a more detailed look on the system. My proposal would be to look at the largest tables and look if they are using LOBs and which sizes the LOBs have. In additon it would be a starting point to compare MSS and SYB-sizes on a table level (for e.g. the 50 largest tables).

If you like you can open a ticket on component BC-DB-SYB and we can have a look on the system.

Regards,

Jan

HuseyinBilgen
Active Contributor
0 Kudos

Hi Jan,

Here is the  MSSQL version of the same table which is half in size compared to ASE version.

My versions during import are above the ones mentioned in SAP Note 1591424 - SYB: 7.02 Heterogeneous system copy with target Sybase ASE

- ODBC 15.7.0.105

- DBSL 720_EXT UC 64, pl 220

- R3load 720_EXT UC 64, pl 210

Regards

0 Kudos

Dear Huseyin,

I think it might really be interesting to compare the two databases adn the largest tables in each other .

I suggest you open a ticket at SAP so we can have a look.

The statement to collect the largest tables in ASE would be something like:

=============================

declare @dbid int
select @dbid = db_id('<DBSID>')

--print out table size in MB
select top 25 left(so.name,16) as 'Table Name',
(reserved_pages(@dbid,object_id(so.name),0) * 16 + reserved_pages(@dbid,object_id(so.name),255) * 16 ) / 1024 as 'Total Data Size',
(reserved_pages(@dbid,object_id(so.name)) * 16) / 1024 as 'Total Object Size'
from sysobjects as so join systabstats as st on so.id = st.id
where so.type = 'U'  and st.rowcnt > 1000
order by'Total Data Size' desc
go

============================

Adapt to your needs.

Run the statement in isql as user SAPSR3 in your Sybase ASE server .

I can't tell you the best way to select the largest tables in a database an MSSQL server, but could try to find out if you need help with it.

With best regards

Tilman Model-Bosch

stephan_romanith
Explorer
0 Kudos

Hello Huseyin,

I can confirm your experiences migrating a MSSQL-DB to Sybase.
I compared the DB-Sizes too, but I installed the ERP 6.0 EhP5 IDES System on both plattforms and here are the data sizes inside the DBs (not the DB-size!):

- Windows 2008 R2 with MSSQL 2008 R2 with page compression: 95 GByte

- Windows 2008 R2 with Sybase 15.7 Patch 12 with page compression: 180 GByte

Regards, Stephan

JanStallkamp
Employee
Employee
0 Kudos

Hello Stephan.

I've talked to some colleagues about this issue and we have some ideas what might cause this behaviour. If you like we can have a look at your systems and analyze this further. The easiest way would be to open a message on BC-DB-SYB and mention this SCN conversation.

Regards,

Jan

HuseyinBilgen
Active Contributor
0 Kudos

Hi all,

I just re-migrated another database with 350GB and write a blog about it. You may want to check it

0 Kudos

Hi,

thank you for your report. Did you or anyone else migrate from Oracle and check the size?

br

Peter

HuseyinBilgen
Active Contributor
0 Kudos

Actually I wanted to do it. But have no time to do it as a PoC. We'll do it for 2 customers soon and I'll share the results.

Former Member
0 Kudos

I am trying to Migrate ECC ehp5 with IDES on Oracle to Sybase ASE.

I am also having this issue regarding database size... even after deleting clients 810, 811, and 812 from Oracle (export file was around 60 gigs) the required free hdd space for a migration was 547 GB...

It seems like the empty tables after deleting clients are still around and the IDES database which is around 270-80 GBs increases to 547... this is just crazy!!!

Anyone know how can I get rid of so much allocated space with yet so many blank tables?

former_member188958
Active Contributor
0 Kudos

Speaking from a pure ASE perspective (i.e. no knowledge of ECC or IDES),

historically when a table or index was created ASE allocated an 8-page extent for it, even if there was not yet any data.  As the SAP applications create thousands of tables, many of which may never be populated or used, a new "deferred_allocation" enhancement was added to the CREATE TABLE command that delayed allocation of any disk space until data was first inserted into the table. 

http://infocenter.sybase.com/help/topic/com.sybase.infocenter.dc00641.1572/html/new_features/new_fea...

The design specificationfor the feature noted that a possible future enhancement would be to automatically deallocate all space when a table was truncated, but to the best of my knowledge this has not yet been implemented, nor do I find a formal change request (CR) opened for it.

I think what needs to be done to free up the space is identify the empty tables that have space allocated to them, drop those tables, and recreate them using the CREATE TABLE ... WITH DEFERRED_ALLOCATION option.

JanStallkamp
Employee
Employee
0 Kudos

Hi.

A general remark: Please open new threads if you have a question and don't put everything in older discussions. This makes it way easier to find content in SCN.

To your question: This space increase doesn't look normal to me. Have you read Hüseyin's blog about his experiences? I added some comments there, too.

Bret is right about the deferred table creation and this will save some GB. But the space increase you notice is huge, so I would expect that something else has been going wrong with the migration. Have you checked SAP Note 1680803? You will find a lot of useful information about migrating to Sybase ASE.

Regards,

Jan

Former Member
0 Kudos

I think that would require too much time to recreate all tables (sorry new to this) wouldn't it?

Former Member
0 Kudos

I will post what I have done tomorrow and walkthrough my experience but thanks for the tip! I will dig into that note and yes I have read the blog but I am quite the newbie with Sybase

Answers (3)

Answers (3)

Former Member
0 Kudos

Greetings,

The database size does not depend on the page size specified for the server.


I believe that SAP no longer supports 32 bit version ASE servers, so, the page size depends only on the version of ASE that you are currently using. It is either 32 Terrabytes, or 64 Terrabytes, depending on wether you have a version of ASE < 15.7 or a version of ASE >= 15.7 .


See.

http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.infocenter.dc32300.1600/doc/html/title...

Regards,

Jean-Pierre

crisnormand
Active Participant
0 Kudos

Hello,


This thread was initially about the maximum database size on SAP ASE.

The maximum database size allowed does depend on the logical page size specified for the dataserver, you can find detailed information about this in the Configuration Guide (Database Specifications).

Please note this thread has more than two years old, so please open a new thread if you'd like to start a new discussion on this or similar subject.

Thanks

Regards,

Cris

Former Member
0 Kudos

ASE 15.7 ESD#2 which is released in Aug 2012 supports maximum single ASE database of size   8TB ( for 2k logical page size server) or 64TB (for 16K page logical page size server).

If you want it to confirm , please check in Rob's forum/blogs. Thanks.

Former Member
0 Kudos

This message was moderated.