cancel
Showing results for 
Search instead for 
Did you mean: 

how to re-enabled the threshold sp?

Former Member
0 Kudos

I have a threshold set on log segment with action procedure.

Then I increased log segment space and got message said the threshold disabled.

this is the info from

sp_helpthreshold 'logsegment'

go

segment namefree pageslast chance?threshold procedure
1logsegment3000000dbo.sp_thresholdaction
2logsegment3000161dbo.sp_thresholdaction_lastchance

Here is what I am trying to do: drop it and recreated it again:

---1. drop one, it is okay.

EXEC sp_dropthreshold 'mydb','logsegment',300000

go

--2. drop another one:

EXEC sp_dropthreshold 'mydb','logsegment',300016

go

---got followinng error message:

Procedure (sp_dropthreshold) You may not drop the log's last-chance threshold.

--3. try to put it back:

EXEC sp_addthreshold 'mydb','logsegment',300000,'dbo.sp_thresholdaction'

go

---got following message

Procedure (sp_addthreshold) This threshold is too close to one or more existing thresholds.  Thresholds must be no closer than 128 pages to each other.

How to resolve this problem?

jgleichmann
Active Contributor
0 Kudos

just to supplement Mark's post:

To get the LCT you can use as already mentioned lct_admin:


select lct_admin('lastchance', db_id)

The db_id you get for example from sp_helpdb.

If lct_admin(“lastchance”, dbid) returns zero, the log is not on a separate segment in this database, so no last-chance threshold exists.

Regards,

Jens

Accepted Solutions (1)

Accepted Solutions (1)

jgleichmann
Active Contributor
0 Kudos

Hi,

you can not drop the LCT (last chance threshold), so you can not recreate it, because it is still present.

The LCT is scaling automatically to the size of the logsegment. The one threshold was disabled because it comes to close to the LCT (128 pages).

Just create one over 300154 pages.

Regards,

Jens

Former Member
0 Kudos

Thank you, Jens. It's wotking. But curious have 2 questions:

1. How that 2 thresholds with free page 300016, 300000 in pace before? Some one created before with 5G logsegment. When try it, the logsegment is 10G. is it related to logsegment size?

2. How to check if a threshold working? For example, for this case, when logsegment increased to 10G, the one with 30000 free page not working. Ho can if know it not working anymore?

3. How to remove the last chance threshold if I want to reset everything for this case?

Answers (0)