Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1204
  • Last Modified:

Sybase sp_thresholdaction

I have a following sp_thresholdaction in ASE 15. I have added another threshold in db UPMVat, but now I'm just curious when I should trigger the action? or ASE will do automatic sp_thresholdaction when the db UPMVat reach the threshold defined ?


1> sp_helptext sp_thresholdaction
2>
 # Lines of Text
 ---------------
               1

(1 row affected)
 text                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          
 ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 create proc sp_thresholdaction (@dbname varchar(30), @segment_name varchar(30), @space_left int, @status int)
as
begin
      dump tran @dbname with truncate_only
      return 0
end
                                                                                                                                                                                                                                                                                                                                                   

(1 row affected)
(return status = 0)
1> use UPMVAT
2>
1> sp_helpthreshold
2>
 segment name                 free pages               last chance?                 threshold procedure                        
 ---------------------------- ------------------------ ---------------------------- ------------------------------------------
 logsegment                            500                          0               sp_thresholdaction                        
 logsegment                            192                          1               sp_thresholdaction                        

(2 rows affected)
(return status = 0)
1>
0
motioneye
Asked:
motioneye
  • 2
2 Solutions
 
Joe WoodhousePrincipal ConsultantCommented:
It will automatically fire the procedure when the thresholds are crossed. There are some details to prevent it firing repeatedly if the number of free log pages wobbles back and forth over the threshold, but in principle it's automatic.

Note that firing the procedure does not mean the log will be truncated. Dumps of the same database serialise, so if for some reason a dump tran is already running, firing the proc will just cause another dump to queue up behind it.
0
 
motioneyeAuthor Commented:
Hi,
As example below, I have two threshold for one of the db, does it means dump transaction will trigger twice when free pages fall at 500 and subsequently as low as below 192 ?


1> sp_helpthreshold
2>
 segment name                 free pages               last chance?                 threshold procedure                        
 ---------------------------- ------------------------ ---------------------------- ------------------------------------------
 logsegment                            500                          0               sp_thresholdaction                        
 logsegment                            192                          1               sp_thresholdaction
0
 
Joe WoodhousePrincipal ConsultantCommented:
It will launch the procedure when every threshold is crossed, yes. Based on your procedure then yes, it will attempt to launch a dump tran with truncate_only.

This still won't guarantee you logs never fill since the dump tran might not be fast enough, or might fail, etc.
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now