cancel
Showing results for 
Search instead for 
Did you mean: 

DB stops with "data area full" but still tons of space left.

Former Member
0 Kudos

Hi list,

after a crash of our db-server there seems to be a problem in the log management. The problem started with a corrupt backup history file, where one line of the history contained "extra" text info so we couldn't open the "backups" view in database studio.

After manually fixing the line everything seemed to work well again. But till then the database stops from time to time with a "data area full" message. After setting the db to offline- or admin-state followed by a db_online the db ist running again, showing a fill-grade of about 70% with aprox. 2GB free space.

Any idea how to fix this?

Accepted Solutions (0)

Answers (5)

Answers (5)

Former Member
0 Kudos

Ashes on my head, i've totaly skipped your question regarding a sap-app.

No, it's not something from sap running on top of the db. It's a springframework-driven web-app using hibernate for the persistence layer and c3p0 as pool-manager.

markus_doehr2
Active Contributor
0 Kudos

> Ashes on my head, i've totaly skipped your question regarding a sap-app.

No problme

> No, it's not something from sap running on top of the db. It's a springframework-driven web-app using hibernate for the persistence layer and c3p0 as pool-manager.

Ok

The quickest approach without having the need to change source code would be the diagnose monitor. And then check how big that suspected join can become.

Markus

Former Member
0 Kudos

Hi Markus,

it seems, that in deed temporary results are causing my problem. The temp-space grows to more than 50% of the size of the whole database. Could you give me any hint how i can find the statements generating that much temp results? I've skimmed over the database analyser and command monitor tables, but there seems to be now such row like "temp space used"...

holger_becker
Employee
Employee
0 Kudos

Hi,

unfortunately there is no automatic way to detect those temp space consumers.

Like Markus I would suggest to enable the command monitor feature and have a look into

the system table running_commands when you see an increase of the used temp space.

To enable the command monitor you could use this command: diagnose monitor parseid on

This will store all commands which are prepared in the system table sysparseid.

But be aware that this table could get very huge when a lot of commands are prepared.

So be careful.

As we are currently redesigning the command monitor implementation I will discuss the possibilty to detect such temp space consumers automatically in the future with the responsible developers.

Kind regards

Holger

Former Member
0 Kudos

Hi Markus,

seems our conversation is somewhat out of sync

I've skimmed through our server-logs and it looks as if you are right. I've found log entries saying "space for user session exhausted". If i got it right, this means that there is no temp-space left?

I'll try the analysis you've suggested and further investigate the queries that are created (we are using hibernate, so i'll have to add some debug-logging) But it seems to be a simple two-table join that causes the problem.

markus_doehr2
Active Contributor
0 Kudos

Not out of sync - I just didn´t get e. g. the answer if this a SAP system running on top or not

You can also switch on the diagnose monitor (see - posting by Lars). If this situation reoccurs you can do a

select * from running_commands

to see which statement is being executed on the database.

Markus

Former Member
0 Kudos

Hi Markus,

ok, i understand. But we are running in this configuration for about two years without problems.

Sizes are (after adding the three 2GB volumes):

Total: 17GB

Perm: 7,9GB

Temp: 0,68 GB

Free: 9,5 GB

I've set the db to admin when about 500MB where free, so nearly 9GB have been consumed somehow. After that, they are free again. Is it possible that this 9GB are temporary space?

markus_doehr2
Active Contributor
0 Kudos

I can´T tell you what´s happening... you may encounter a long running statement that keeps creating temp pages. Without knowing what application is running on top and what that application is doing I can´t help you...

Markus

Former Member
0 Kudos

I've now seen this "live".

The database started to fill up, so i added 3 data volumes of 2 GB each. They all are consumed in about 10 minutes - but: there are no logs written. Could this be some internal space used by the db? After the steps db_admin -> db_online the consumed 6 GB are free again.

markus_doehr2
Active Contributor
0 Kudos

Yes. This can be because of temporary data.

Without knowing what the application is doing it very difficult to suggest something.

You can turnon the dbanalyzer with e. g.

dbmcli -u <dbm>,<dbmpw> -d <SID> dban_start -t 30

This will create an "analyzer" directory in the rundirectory of the instance which may give you more information what´s going on on database level.

Is this a SAP application running on top?

Markus

markus_doehr2
Active Contributor
0 Kudos

Depending on the size of the system 2 GB free space is not much.

Imagine e. g. if your application does a big join where temporary pages are created in the database. This can fill up the data area pretty quickly although the permanent data doesn´t occupy everything.

Markus