Results 1 to 9 of 9
  1. #1
    Join Date
    Jan 2004
    Posts
    3

    Unanswered: contol tempdb log

    The size of tempdb log is continously getting bigger, is it possible to make this dumped as it becomes greater that a specific value (let's say 80%) ?

  2. #2
    Join Date
    Jan 2004
    Posts
    32

    Re: contol tempdb log

    You can create “Thresholds” to which are used to monitor space on a database segments. When free space on the segment falls below the specified level, Adaptive Server executes the associated stored procedure. You can also use thresholds to automate backup procedures.

    Implementation of threshold in Sybase is a two-step process.
    1) Create a stored procedure, which does the required action, for example to dump the database or the transaction log or just print the remaining pages left in the segment in the error log.
    2) And create thresholds in a database to tell the ASE when to execute the above stored procedure.

    For more detail information, read “Managing free space with Thresholds” in System administrator guide. (http://sybooks.sybase.com/onlinebook...s/asg1251e/sag)

  3. #3
    Join Date
    Jan 2004
    Posts
    3
    Thanks ssglb for ur immediate response, I'm trying to do it (since i'm a poor beginner)

  4. #4
    Join Date
    Feb 2004
    Posts
    28

    Re: contol tempdb log

    Try increasing the size of tempdb as per your need. I am not sure if you can put a threshold on tempdb.

    Originally posted by etaoufik
    The size of tempdb log is continously getting bigger, is it possible to make this dumped as it becomes greater that a specific value (let's say 80%) ?

  5. #5
    Join Date
    Sep 2003
    Location
    London
    Posts
    12
    I think the problem with tempdb is that the thresholds will disappear when the server is restarted, hence you need to re-add them (perhaps create a startup script to do it...).

    Will
    Will

  6. #6
    Join Date
    Jan 2004
    Posts
    3
    Hi all,
    I think this could be solved dropping temporary tables clsxxxx_xx, I just make sure before doing this that there's no more transactions on tempdb running.

  7. #7
    Join Date
    Nov 2002
    Location
    Switzerland
    Posts
    524
    1) put your threashold in the model database (each reboot copies the model into the tempdb)
    2) sp_dboption tempdb, "trunc log", true

  8. #8
    Join Date
    Sep 2003
    Location
    London
    Posts
    12
    Yeah, make sure its got "trun log on checkpoint", or even "abort tran on log full". Though the temp tables should be dropped automatically by the application.
    As for thresholds - you can't always add them to model, since your model db is prob 2Mb but your tempdb could be many Gbs...
    Will

  9. #9
    Join Date
    Feb 2004
    Posts
    28
    Here are my thoughts on tempdb space usage/monitoring

    - Putting thresholds on a tempdb is not a good idea as the db option - "trunc. log on chkpt" is always there to trun the log.

    - You do not need to explicitly set "trunc. log on chkpt" option for tempdb as it is always implicitly applied by ASE, regardless of whether you set this option or not.

    - Remember, ASE truncates the the log upto the last open transaction, if you have an open transaction towards the end of your log then ASE will clear up the log ONLY upto that transaction, rest of the log(before that transaction) will NOT be cleared. (Sybase logs are written sequentially).

    - In a production running server, you dont want tempdb to be fill upto 80%. You do not want simultaneous threads in ASE to compete for tempdb space. Aim for 50% max usage. You will be safe.

    - Estimate your tempdb space requirements. Begin with application monitoring. Look for long running transactions. Check your batch jobs. There is an excellent information in Sybase admin guide (or performance tuning guide) for estimating tempdb size. Sorry I do not have the link at this moment.


    j

Posting Permissions

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