Results 1 to 5 of 5
  1. #1
    Join Date
    Oct 2003
    Posts
    2

    Unanswered: TempDB restrictions

    Hi,

    I was looking at the solutions to the issue of tempDB in Sybase getting full. Obvious solutions suggested that tempDB size can be increased while the cause of the problem can quite often be identified as a bad query.

    I have a situation where my logs supposedly indicate a query is reponsible to fill up the tempDB. However I am not quite convinced with that as that query runs at regular interval for processing some records and we have not had the issue before. (FYI, the tempDB we are using has got a size around 2000MB.)

    Could someone let me know if it is a possible scenario that a (bad) query is responsible for filling up the tempDB (sometimes) but actually does not do that every time it runs.

    Any pointers to get more info towards this is appreciated.

    Thanks

  2. #2
    Join Date
    Sep 2003
    Posts
    17

    Re: TempDB restrictions

    Hi,

    The most important thing is to find out which query it is. The query might have parameters like time ranges, which returns more or less data, dependent on the time range chosen. Apart from that, other users could use tempdb space at the same time as well.

    In order to prevent tempdb from filling up (which causes the whole server to hang) I would recommend - as far as you run on 12.5 - to use the "Resource Governor", which allows you to restrict the space of tempdb used by a single query.

    Regards,
    Ulrike

    Originally posted by satyajitmishra
    Hi,

    I was looking at the solutions to the issue of tempDB in Sybase getting full. Obvious solutions suggested that tempDB size can be increased while the cause of the problem can quite often be identified as a bad query.

    I have a situation where my logs supposedly indicate a query is reponsible to fill up the tempDB. However I am not quite convinced with that as that query runs at regular interval for processing some records and we have not had the issue before. (FYI, the tempDB we are using has got a size around 2000MB.)

    Could someone let me know if it is a possible scenario that a (bad) query is responsible for filling up the tempDB (sometimes) but actually does not do that every time it runs.

    Any pointers to get more info towards this is appreciated.

    Thanks

  3. #3
    Join Date
    Feb 2002
    Location
    Willy is on vacation
    Posts
    1,208
    You could use the multiple tempdb feature in 12.5.0.3 onwards. With this feature, you could bind logins to their own tempdb's. So rogue users can do whatever they want to fill their own tempdb and this ain't gonna affect other users. You can bind the dba to the default system tempdb for admin tasks only.

  4. #4
    Join Date
    Oct 2003
    Posts
    2
    Thanks all for the answers.

    Apparently, I have got another question in store.

    Is there any way I can get the usage of TempDB by the processes.
    What I mean is, if I can know which process if taking heavy amount of space in TempDB and the time at which it has taken the space.

    That will probably help us investigate the causes (queries) of TempDB getting filled up.

  5. #5
    Join Date
    Apr 2003
    Posts
    54
    Satyajit,
    I do not know whether you have tried this before or not. You can monitor the usage of tempdb 1. via sybase central (default segments)
    2. After executing the query which you think might be filling up the tempdb, you can check the table master..syslogshold to see which is the open transaction when the tempdb is full and all transactions are suspended. Also, I got to know a few days back that sybase has recognised a bug related to tempdb filling up so(I do not have the CR# though). Can you post the query???? Also, please post the @@version.

    Hope this helps..........

    Regards

    jaideep

Posting Permissions

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