Shrinking / Reorganizing database size using (Max-DB)

[Former Member]

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

[Former Member · Accepted solution]

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 · Accepted solution]

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 · Accepted solution]

>

> 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