on 02-10-2009 10:14 AM
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?
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.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
> 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
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"...
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
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.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
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?
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
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
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 | |
11 | |
9 | |
7 | |
6 | |
5 | |
5 | |
5 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.