Results 1 to 4 of 4
  1. #1
    Join Date
    Jun 2009
    Location
    India
    Posts
    50

    Unanswered: Monitoring tempdb space

    Hi,

    One of the developers has got below error

    Database error code : 20018

    Database error message :


    Can't allocate space for object "temp worktable" in database "tempdb" because system segment is full/has no free extents. If you ran out of space in syslogs, dump the transaction log. Otherwise, use ALTER DATABASE to increase the size of the segment.

    I have add tempdb size by 1 GB, which has got issue resolved.

    Why did ASE poped out error stating system segment is full?
    Where exactly does object "temp worktable" reside?
    Is there any way that I can monitor the space used by tempdb @ runtime... i.e i do not want above error to be poped out.

    Could some one respond on above query

  2. #2
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1
    The system segment store mostly system related info i.e. table info sysobjects, sysindexes, etc.
    Have a look at sp_dbextend
    to set thresholds and auto expand

  3. #3
    Join Date
    Mar 2009
    Location
    Sydney, Australia
    Posts
    258
    First you need to understand worktables. You can get an idea of these by inspecting the showplan output; worktables are created/populated/used when an intermediate table is implied, eg:
    - a sort is required, eg. due to ORDER BY
    - aggregation: SUM(), AVG(), etc
    - an intermediate table required to resolve a join where the predicates do not exist (and they are defined in the sql being executed).

    The fact that "system" segment gets reported as full is misleading: in tempdb, unless you have done some basic P&T and separated the system/default/log segments, the three segments are all mixed; what happened was a worktable on default segment filled tempdb; which means all three segments filled; at some point an object creation was required (eg. the next worktable), which hit system segment; which produced the error. You can take it that it was actually a full default segment that was the cause, not the worktable on system segment that was reported (that was just the straw that broke the camels back).

    As long as you are using tempdb heavily, as the db size grows, tempdb will grow; sometimes exponentially (where tempdb is overused). So auto-expand, etc certainly works, but that is a never-ending expansion. You really need a method of (a) monitoring the objects in tempdb and ensuring they do not monopolise tempdb and hang up other users, and (b) reducing tempdb overuse overall [post if you want an explanation].

    Monitoring all tempdb objects over time, provides the best, most informed view of the actual situation, the build-up that lead to the fill (the single command or #table or worktable that reported an error is not the actual problem). This allows you to make informed decisions, curtail users, even kill processes before tempdb fills and hangs everyone up.

    You can alternately use "user" tempdbs to contain errant users. I do not recommend it because it requires a fair amount of manual administration, "spreads the pain",and it only delays the dealing with the problem, which eventually must be done.
    Last edited by Derek Asirvadem; 08-31-09 at 20:00. Reason: Minor typos corrected
    Regards
    Derek Asirvadem (Formerly DerekA)
    Information Architect / Senior Sybase DBA
    Copyright 2009 Software Gems Pty Ltd

    I answer questions from the Original Poster only. If you have a genuine question, as the moderators have requested, start a new thread.

    http://www.softwaregems.com.au

  4. #4
    Join Date
    Mar 2009
    Location
    Sydney, Australia
    Posts
    258
    Monitoring all tempdb objects over time, provides the best, most informed view of the actual situation, the build-up that lead to the fill (the single command or #table or worktable that reported an error is not the actual problem). This allows you to make informed decisions, curtail users, even kill processes before tempdb fills and hangs everyone up.
    If you are interested in a high-performance, low-impact, commercial monitor that does exactly that, provide a serious overview of the actual contents of tempdb; and optionally kill runaway processes (eg. that exceed a percentage threshold), send me an email. It runs in production at several sites, and it is particularly useful where you have a third-party app (you cannot change the SQL code that causes the problem, so you have to manage the problem instead).
    Regards
    Derek Asirvadem (Formerly DerekA)
    Information Architect / Senior Sybase DBA
    Copyright 2009 Software Gems Pty Ltd

    I answer questions from the Original Poster only. If you have a genuine question, as the moderators have requested, start a new thread.

    http://www.softwaregems.com.au

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •