cancel
Showing results for 
Search instead for 
Did you mean: 

Shrinking / Reorganizing database size using (Max-DB)

Former Member
0 Kudos

Dear Community,

we are running an ECC 6.0 on Max-DB, which was delivered to us as all-for-one solution by SAP a few years ago.

According to TA DB50 the database size is 100 GB, 78% used. Well, this is a little confusing, because even the sum of the 1.000 biggest table sizes converge at approx. 50 GB, so there is no way all table sizes could have a size of 100 GB. All other databases in our landscapes occupy 50GB - 60GB.

We deleted the following things:

- four whole clients via TA SCC5

- job-logs,

- shortdumps,

- spool-lists,

- temse-objects.

We can also verify all those actions mentioned above worked out properly and neither job-logs, shortdumps, spool-lists, nor temse-objects occupies much space in db.

We are aware of the fact that the new space after a client deletion only gets freed after a reorganizing the database, but we don't know how to do that in max-db and all information we find is: u201CMax-DB

does not need a manual reorganization.u201D

We're wondering where those 50 GB u201Chidden spaceu201D could have been left over. As we were unable to find a manual way to reorganize the max-db database, we'd appreciate if you gave as a clue how to shrink the database from 100 GB to approx. 50 GB or 60 GB, as we believe there are at least 40 GB which are not used.

Greetings

Danny

Edited by: dwinn5 on May 6, 2010 12:44 PM

Accepted Solutions (1)

Accepted Solutions (1)

Former Member

Hi Lars,

thanks for your advice. I'm currently running a consistency check as recommended by your URL. This will take a few ours.

I also found commands for creating and deleting snapshots. I was unable to find information about how to determine a snapshot's size.

Regards,

Danny

Former Member
0 Kudos

Hi,

I checked sizes using sql-commands on maxdb-level. The result is even more confusing.

Check this out:

1.) Printing the sum of <sapsid> Schema:

*select * from sysinfo.schemasize where schemaname='<sapsid>'*

= 100 GB !

2.) But calculating the sum of <sapsid> Schema:

select sum(usedsize/1024/1024) from sysinfo.tablesize where schemaname='<sapsid>'

= 65 GB !

So, we know now that all the space is used in the <sapsid>-schema. But how can the current

schemasize result in 100 GB while recalculating it results in 65 GB? This seems impossible.

Regards,

Danny

lbreddemann
Active Contributor
0 Kudos

>

> Hi,

> I checked sizes using sql-commands on maxdb-level. The result is even more confusing.

>

> Check this out:

>

> 1.) Printing the sum of <sapsid> Schema:

>

> *select * from sysinfo.schemasize where schemaname='<sapsid>'*

>

> = 100 GB !

>

>

> 2.) But calculating the sum of <sapsid> Schema:

>

> select sum(usedsize/1024/1024) from sysinfo.tablesize where schemaname='<sapsid>'

>

> = 65 GB !

>

> So, we know now that all the space is used in the <sapsid>-schema. But how can the current

> schemasize result in 100 GB while recalculating it results in 65 GB? This seems impossible.

Please run the following statement and post the output:


select 1 as lineno, 'TABLESZ',sum (usedsize) usedsize from sysinfo.tablesize where schemaname='SAPSID'
union all
select 2, 'INDEXSZ',sum (usedsize) usedsize from sysinfo.indexsize where schemaname='SAPSID'
union all 
(select 3, 'TAB+IDX', sum(usedsize) usedsize from
(select 'TABLESZ',sum (usedsize) usedsize from sysinfo.tablesize where schemaname='SAPSID'
union all
select 'INDEXSZ',sum (usedsize) usedsize from sysinfo.indexsize where schemaname='SAPSID'))
union all
select  4, 'SCHEMASZ', usedsize from sysinfo.schemasize where schemaname='SAPSID'

regards,

Lars

Answers (2)

Answers (2)

Former Member
0 Kudos

Hi,

LINENO

EXPRESSION1

USEDSIZE




3

TAB+IDX

1.03517576E8

4

SCHEMASZ

1.03517576E8

1

TABLESZ

6.9612784E7

2

INDEXSZ

3.3904792E7

4 rows selected (38.870 sec)

So that means the sum of tablesize (69 GB) and Indexsize (34 GB) result in 103 GB fill-level and there is no way shrinking it, right?

Regards,

Danny

lbreddemann
Active Contributor
0 Kudos

> So that means the sum of tablesize (69 GB) and Indexsize (34 GB) result in 103 GB fill-level and there is no way shrinking it, right?

Yep - looks like the numbers correctly add up here.

regards,

Lars

Former Member
0 Kudos

thanks,

i appreciate your help. the problem is solved.

regards,

danny

markus_doehr2
Active Contributor
0 Kudos

> According to TA DB50 the database size is 100 GB, 78% used. Well, this is a little confusing, because even the sum of the 1.000 biggest table sizes converge at approx. 50 GB, so there is no way all table sizes could have a size of 100 GB.

Be aware that all indices are not taken into account in that statistics.

> We are aware of the fact that the new space after a client deletion only gets freed after a reorganizing the database

No - MaxDB frees the space automatically, no reorganization is needed.

> We're wondering where those 50 GB u201Chidden spaceu201D could have been left over.

Run report RSADAT6M - and check the job log/spool. This contains all the data including the index sizes.

Markus

Former Member
0 Kudos

Hello Markus,

thanks for your advice so far.

I ran report RSADAT6M as you recommended. The result was exactly what I expected: 65 GB size (sum of object-size column). The index-sizes were included.

But looking at TA DB50's database fill level, which I also recalculated by refreshing statistics, I see 100 GB occupied. The database manager also shows 100 GB used area.

There must be 35 GB hidden somewhere. The question is: Where? And how do we get rid of it?

Greetings

Danny

Edited by: dwinn5 on May 6, 2010 3:03 PM

lbreddemann
Active Contributor
0 Kudos

Hi there!

There are multiple possible reasons for that:

1) The DB50 and the report only consider the tables/indexes/lobs owned by the SAP-DB-User that is maintained in the DB50 integration data. That means, usually you only see the ABAP schema, but not the J2EE schema if you use one.

You may setup a separate integration via DB59 for the J2EE schema to review the tables used there.

2) It may be possible that there is a SNAPSHOT hold in the database. This would allocate space in addition to the table sizes.

3) The freespace management may have went a bit weired.

Run a CHECK DATA WITH UPDATE in ADMIN mode and check wether pages have been released.

See my blog [MaxDB: Space oddities|http://www.sdn.sap.com/irj/scn/weblogs?blog=/pub/wlg/12959] [original link is broken] [original link is broken] [original link is broken]; on this rather special case.

regards,

Lars

Former Member
0 Kudos

Hi Lars,

sounds good.

I ran the command CHECK DATA WITH UPDATE Using sqlci as SUPERDBA, but got the error-message that this "sql-statement is not available for this application", so where do I have to run it?

How can we discover if there are any snapshots incl. their sizes?

Sorry, this is our first contact with max-db.

Greetings

Danny

lbreddemann
Active Contributor
0 Kudos

>

> Hi Lars,

> sounds good.

>

> I ran the command CHECK DATA WITH UPDATE Using sqlci as SUPERDBA, but got the error-message that this "sql-statement is not available for this application", so where do I have to run it?

>

> How can we discover if there are any snapshots incl. their sizes?

>

> Sorry, this is our first contact with max-db.

Hi again,

for all those how to questions there's a pretty well filled wiki (not to mention the documentation...):

http://wiki.sdn.sap.com/wiki/display/MaxDB/Checkdatabasestructure

Check this and you should get all information you need.

regards,

Lars