Results 1 to 9 of 9
  1. #1
    Join Date
    Jan 2009
    Posts
    4

    Unanswered: sybase threshold problem

    I am a newbie in with databases. I working on a system with the sybase database. I am having a problem with understanding and using the concept of thresholds.

    From what I have understood so far, when we create a database, sybase creates a datafile for the data and a logfile for recording the transaction logs.The datafile is created in a place called the datasegment and the logfile is created in a logsegment. On the logsegment, there is a built in threshold called the 'last chance threshold', when this is reached, the transactions are halted and the log must be dumped. If we want, we can add an additional threshold before the last chance threshold so that logs can be dumped before. (As I said, I am a newbie so correct me if this is wrong)

    My question is that if there are more than one database created, they will have their separate log files placed on the logsegment. How can i place a threshold on the logs of these individual databases so that for example, when the log of database A is 80% full it is dumped. Is this possible ?

    From the manual, I found a command :

    Code:
    sp_addthreshold dbname, segname, free_space, proc_name
    will this command serve my purpose? if not is there any other possibility?

  2. #2
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1
    Yes with sp_addthreshold, when free space on the segment falls below the specified level, Adaptive Server executes the associated stored procedure

    However a data file is not created on a segment.
    A segment is created on a file.
    So it is entirely possible but not good practice
    to have a log segment on a "data file"
    or a data segment on a "log file"
    or both a data and log segment on a file

    Also consider doing a dump in procedure sp_thresholdaction

  3. #3
    Join Date
    Jan 2009
    Posts
    4
    Thanks for the reply. Another thing that I wanted to clarify is that in the command:

    Code:
    sp_addthreshold dbname, segname, free_space, proc_name

    what exactly is the parameter "dbname". Is it the name of the database from where the procedure "proc_name" will be picked up or is it the name of the database for whose logsegment the threshold will be added ?
    (I have assumed that in the sp_addthreshold the segname is the logsegment of our database)

  4. #4
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1
    Quote Originally Posted by batman727
    From the manual, I found a command :
    Code:
    sp_addthreshold dbname, segname, free_space, proc_name
    Quote Originally Posted by batman727
    what exactly is the parameter "dbname". ...
    is it the name of the database for whose logsegment the threshold will be added
    Yes, the database for which to add the threshold
    Can be any segment in the database as specified by segname
    Reference Manual: Procedures
    Chapter 1 System Procedures
    ...
    sp_addthreshold
    ...
    Parameters
    dbname
    is the database for which to add the threshold...
    segname
    is the segment for which to monitor free space...
    ...

    Crossing a threshold

    When a threshold is crossed, Adaptive Server executes the associated stored procedure. Adaptive Server uses the following search path for the threshold procedure:

    * If the procedure name does not specify a database, Adaptive Server looks in the database in which the threshold was crossed.

    * If the procedure is not found in this database, and the procedure name begins with "sp_", Adaptive Server looks in the sybsystemprocs database.

    If the procedure is not found in either database, Adaptive Server sends an error message to the error log.

  5. #5
    Join Date
    Jan 2009
    Posts
    4
    OK. I understand now. I have executed the sp_addthreshold command and have added a threshold to the logsegment of my database.

    Now, how can I check that where and what thresholds are added on the logsegment of my database. Is there any command I can use for that.

    For example, I have added a threshold at 80% of the logsegment of my database and now I want to "see" that threshold. How can I do it.

  6. #6
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1
    sp_helpthreshold [segname]

    Reports the segment, free-space value, status, and stored
    procedure associated with all thresholds in the current database or
    all thresholds for a particular segment.

  7. #7
    Join Date
    Jan 2009
    Posts
    4
    OK, so far so good. I have cleared my concepts about thresholds and segments. I have installed sybase on my pc and have played around with it. I created a test database and added a thresholds to it.

    Now for the real world problem.I am working on a database which is already running on a live system. on that database when I run the
    sp_helpdb dmname command it gives me:

    select into/bulkcopy/pllsort, trunc log on chkpt, mixed log and data
    does this mean that in this database the logsegment and datasegment are not separate?

    Also, can this situation cause some sort of problems? For example what happens in this case when the log gets filled, will we observe some abnormal
    behavior ? In this case, when the log and data are mixed, how can I find out how much space the log is taking and how much space was originally allocated to the logsegment.
    Last edited by batman727; 01-29-09 at 00:20.

  8. #8
    Join Date
    Feb 2009
    Posts
    1
    I guess you need to create database with log and data on different devices and could not use dump to move the database into the new one.

  9. #9
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1
    Read up on sp_logdevice

Posting Permissions

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