on 05-04-2012 3:46 PM
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
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
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
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
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
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
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
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
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?
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.
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.
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
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.
Regards,
Jean-Pierre
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
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.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
This message was moderated.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
84 | |
24 | |
12 | |
9 | |
7 | |
6 | |
5 | |
5 | |
4 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.