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 ?
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
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)
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
... 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.
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.