0

We have SAP Solution Manager running on a SAP Sybase ASE 15.7 Database. That means we have a user database holding the application data, and a special tempdb exclusively mapped to this user database.
Over the last weeks, the database server has been loggging messages like the following:

Can't allocate Space forobject 'temp worktable' in database 'saptempdb'  
because 'system' segment is full/has no free extents.[...]

So far so good. I have been extending the device and the database segment over and over up to a size of now 150 GB but the message keeps appearing. From the application administrators, I keep getting the statement that there is no possibility to further tune the application's configuration. I have been trying to manually get more information on what is really happening on the tempdb whenever the log message appears (for example by querying the sysusages table), but it seems I can not catch the database red-handed when it tries to use all the space in the system segment of saptempdb.
Does anyone have any tips or procedures for properly monitoring a temp database and finding the statements or transactions which cause a demand for huge temporary worktables?

Sandra Rossi
  • 11,934
  • 5
  • 22
  • 48
Laghorn
  • 43
  • 9

2 Answers2

0

There can be many reasons why space is consumed in your tempdb. You may get some insights by regularly querying the MDA table monProcessObject.

RobV
  • 2,263
  • 1
  • 11
  • 7
0

By default the 'system' segment is co-located with the 'default' segment (ie, both segments reside on the same set of device fragments). Where this can differ is if the 'system' segment has been modified to reside on fewer and/or separate device fragments than the 'default' segment.

I'm wondering if the 'system' segment may have been modified to reside on only a smallish device fragment, while the extension of the database (to 150 GB) may not have increased the space available to the 'system' segment.

It's not clear what actual steps were taken to extend 'the device and the database segment' (eg, was only the 'default' segment extended?), so would be interesting to see what the following returns:

use saptempdb
go
exec sp_helpdb saptempdb
go

This should provide the details necessary to tell which segments reside on which device fragments, to include showing if the 'system' segment has been limited to a smallish device fragment.

If the 'system' segment does reside on a smallish device fragment, the 'system' segment can be increased in size by extending (via sp_extendsegment) the segment onto other device fragments in the database (eg, onto a device fragment that the 'default' segment is currently sitting on).

markp-fuso
  • 28,790
  • 4
  • 16
  • 36
  • Hi markp, in the meantime, the mentioned problems made our tempdb grow to 300 GB :) unfortunatley I am not sure how to add an image in this comment. But sp_helpdb output shows, that alle three default segments (default, system and log) spread over my two devices which carry the database, so I assume this worked fine. I decided to add a second device when I had to extend it over 200 GB. I always used DISK INIT/RESIZE to initialize or extend the device, and then used ALTER DATABASE to extend the database on the devices. – Laghorn Aug 25 '17 at 11:54