Skip to Content

Sybase TEMPDB space

Hello Experts,

We are facing few issues when our BW team is executing process chain in one of the system with Sybase 15.7 SP50.

They are stopped by a dump and when we see error log we found an error “ Insufficient resources” in temdb. But if we observe free available memory for that database it has only 1.59% utilized. Could you please suggest me how to overcome this?

Also is it required to alter database even it has 98% empty space?

I am attaching screens that could be useful in guiding me.

Best Regards,

Amarnath

1.png (19.3 kB)
2.png (22.0 kB)
3.png (14.7 kB)
4.png (22.0 kB)
Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

2 Answers

  • Nov 29, 2016 at 06:36 PM

    In a lot of cases where a tempdb fills up (w/ error #1105), the offending query is terminated and the space it's using is released/freed-up in tempdb, with the net result being that anyone who looks at tempdb space usage after the fact will see that the tempdb is empty (ie, has a lot of free space). [The point being that addressing space usage issues in tempdb typically requires knowing what was running at the time the tempdb database filled up.]

    ------------

    On the (very rare chance) you've got a misconfigured tempdb (eg, the system segment does not span all devices that make up your tempdb), could you run the following and post back here with the complete output:

    exec tempdb..sp_helpdb tempdb

    Add comment
    10|10000 characters needed characters exceeded

    • (I wrote this before I realized this question is fairly dated, however if the database hasn't been configured via the guide below, this can still be helpful for making performance improvements).

      I agree with Mark. The space will free up once the process goes away. I'm assuming this is SAP on BW due to the saptools database, sapsa user and screen shots from what it looks to be dbacockpit. However if I'm wrong, please let me know. If this is SAP on BW, it looks like it's missing the saptempdb (or is it cut off the screen shot)? Normally there would be a creation of a second temp database where you bind users too.

      Please follow this guide for appropriate sizing and configuration. It has a lot to offer in making performance improvements.

      1680803 - SYB: SAP Adaptive Server Enterprise - Best Practice for SAP Business Suite and SAP BW

      Here's a screen shot showing sizing for SAP on BW from the above guide. As you can see they recommend saptempdb to be quite a bit larger than your current tempdb.

      If this isn't SAP on BW, I'd still say tempdb is fairly undersized at 2GB with a primary database of >300GB. However I don't have experience with non SAP installations so I can't point you to a guide offhand.

  • Nov 30, 2016 at 01:34 PM

    Hello Mark,

    Attaching output. Help me to understand how to check what has been executed at that particular time.

    Best Regards,

    Amarnath

    Add comment
    10|10000 characters needed characters exceeded

    • OK, so no problems with the way tempdb has been configured (ie, the system segment looks ok).

      ---------

      As for monitoring what's going on when the tempdb fills up ...

      Generally speaking you'll need a means of capturing MDA table data.

      As a generic (ie, non-SAP application) ASE DBA I use some custom scripts as well as ASEMON

      Since you're using ASE as the back-end database for a SAP application I'm guessing there should be some option/feature in the DBA Cockpit that captures the MDA table data for you ... ??? [I wouldn't be surprised if there's a kba/note available that describes how to go about capturing, and using, MDA data when using ASE as the back-end database for a SAP application.]