sp_thresholdaction or trunc log on chkpt?
Bret Halford
and not trying to do a normal dump tran to a device. I don't know of
any reason why the first would be forbidden, though the normal dump tran
certainly can't be done if the log has been truncated. You should be
able to do both. Please post the threshold code and error message if
that isn't the issue.
-bret
Cynthia wrote:
>
> Thank you for the feedback. I have discovered in testing though that you
> cannot have both set. For example, if you leave trunc log on chkpt turned
> on for the database, the
> sp_thresholdaction does not do a dump tran...returns a message that says,
> dump database instead........cannot run dump tran with truncate on, use dump
> database instead....or something to that affect.
>
> So, it seems you can have only one or the other for a user database - to
> rely on the stored proc to execute, or to have the trunc log on chkpt turned
> on for the database.
>
> What is the safest and most efficient method of protecting yourself from the
> log filling up? We have Sun SMC monitoring which alerts us when the logs
> are filling as well.
>
> We had a terrible incident last week where unknown to me, the RSSD database
> had its log fill and therefore replication was just sitting there with
> transactions suspended in the RSSD log. I dumped the tran log for this
> database and got it back going again, but this database did not have trunc
> log on chkpt turned on, nor did it have a sp_thresholdaction stored
> proc.....so, it was an oversight, but a costly one.
>
> Thanks,
> Cynthia
> "Bret Halford" <br...@sybase.com> wrote in message
> news:3F27DBFF...@sybase.com...
> >
> >
> > Cynthia wrote:
> > >
> > > Hi,
> > > Could you tell me what is a better scenario for a warm stand-by
> environment
> > > that has several production databases replicated...is it to have trunc
> log
> > > on chkpt turned on for the primary side databases, or is it more
> efficient
> > > to use the stored proc, sp_thresholdaction in each user database with
> trunc
> > > log on chkpt turned off?
> > >
> > > Also, if you create the sp_thresholdaction proc and it is generic to
> dump
> > > tran <dbname> with truncate_only,
> > > can this be placed in sybsystemprocs versus putting this proc in each
> user
> > > database?
> > >
> > > Many thanks
> > > Cynthia
> >
> >
> > Define "efficient". :-)
> >
> > I would do the in-efficient but safer approach of doing both.
> >
> > Using truncate log on ckpt will cause the log to be truncated more
> > often. This is generally good as it evens out the workload. If
> > you only had the last-chance threshold truncating the log, it would
> > cause spikes of activity as the whole log is truncated in one blow.
> >
> > However, it is very rare but does happen that the checkpoint process
> > is killed (say by infected with 11), which stops the truncate log on
> > ckpt option
> > from working. So it is a good idea to have a threshold action in
> > place all the same as a backup.
> >
> > It would also be a good idea to have thresholds placed, not at
> > the last-chance threshold, but somewhat before it, whose purpose
> > is not to truncate the log (though it can also do that) but to (if
> > truncating the log fails to free space) send out an email or page
> > the SA with an alert that the log is getting quite full in time to
> > manually do something about it before the log completely fills.
> > (i.e. there might be a long-running transaction that is holding
> > the log open that possibly needs to be killed).
> >
> > Yes, you can create thresholdaction procedures in sybsystemprocs as
> > long as they are generic and their name starts with "sp_".
> >
> > -bret
Bret Halford
Cynthia
cannot have both set. For example, if you leave trunc log on chkpt turned
on for the database, the
sp_thresholdaction does not do a dump tran...returns a message that says,
dump database instead........cannot run dump tran with truncate on, use dump
database instead....or something to that affect.
So, it seems you can have only one or the other for a user database - to
rely on the stored proc to execute, or to have the trunc log on chkpt turned
on for the database.
What is the safest and most efficient method of protecting yourself from the
log filling up? We have Sun SMC monitoring which alerts us when the logs
are filling as well.
We had a terrible incident last week where unknown to me, the RSSD database
had its log fill and therefore replication was just sitting there with
transactions suspended in the RSSD log. I dumped the tran log for this
database and got it back going again, but this database did not have trunc
log on chkpt turned on, nor did it have a sp_thresholdaction stored
proc.....so, it was an oversight, but a costly one.
Thanks,
Cynthia
"Bret Halford" <br...@sybase.com> wrote in message
news:3F27DBFF...@sybase.com...
>
>