• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 935
  • Last Modified:

how is this line getting into error log of sybase

Hi,
I have a database A which is now 85% full , i want to know how is this line coming in the errorlog for this database A:
server  background task message: Error: 1, Severity: 50, The default segment on A database is now 80 percent

I also have a database B, which is 90% full but no such warning is coming in errorlog for B, i want to understand how this works.

This is not coming through sp_thresholdaction because inside that stored proc the comment is different.

Please advise
0
Deepak_FIL
Asked:
Deepak_FIL
5 Solutions
 
Joe WoodhousePrincipal ConsultantCommented:
There can be more than one sp_thresholdaction. This is due to how Sybase ASE resolves procedure calls.

If a call is made to a procedure, ASE looks for a proc of that name in the database the procedure was called from. If none exists (owned by 'dbo' if the owner wasn't explicitly included in the name), ASE then checks in the sybsystemprocs database... but only if the stored procedure name starts with "sp_".

So you need to look both in database A and in sybsystemprocs.

But basically the only segment messages you get in ASE without some kind of threshold action is the one when the last chance threshold is crossed in the log ("space has fallen critically low") or when you completely fill a segment. In other words, there is definitely a threshold procedure in play here.

The other thing is that a threshold action need not actually be called sp_thresholdaction! It can have any name. You can check what the thresholds are, and what procedures they call, by running "sp_helpthreshold" in each database.
0
 
alpmoonCommented:
I think another point you need to be careful about is segments. Your database might be 85% full overall. But, threshold mecahnism works based on segments. The segment name in the message is "default segment". But, let's say your transaction log segment might be 90% full and you may have 85% full database combined (assuming that 50% of database is log segment).

In short you should focus on how much each segment is full. Otherwise it would be confusing.
0
 
Deepak_FILAuthor Commented:
Hi,

i checked and i found few more sp_thresh_....stored procs in sybsytemprocs database, so that means they must be getting called.

Other queries:
1. what is the default setting for a new database created? are there any thresholds created for default and log segments, if yes then why in my case default segment got 100% full without giving any warning in the error log?
2. what is the formula to calculate the values for sp_thresholdaction
0
Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

 
Joe WoodhousePrincipal ConsultantCommented:
As above, sp_helpthreshold will show you which thresholds have been setup in your database and what procedures have been associated as threshold actions.

1. As I said above, you don't get any thresholds installed by default except a last-chance threshold on the log segment (which is defined as "leave just enough room to right the log records for a "dump tran" command"). There are no thresholds installed by default on any other segment. Which means unless a site installs some, the first you know is when you run out of space.

ie. your scenario where the default segment filled 100% without any early warnings in the ASE error log is correct and expected.

2. There's no formula, it's based on number of pages. You can see the number of pages using sp_spaceused, or you can use sp_helpdb and convert from your database page size to a number of pages. (ie. if you have 100Mb of log, you have a 2Kb page size, then that is 100 x 512 = 51,200 pages)

From there you can decide what percentages you want. Fairly common ones are 50% + 75% + 90%, but it's totally up to you.
0
 
Deepak_FILAuthor Commented:
i can see these 2 thresholds in my database :

EXEC sp_addthreshold 'fmr_reference','default',158720,'sp_thresholdaction'
EXEC sp_addthreshold database_name_A ,'default',317440,'sp_thresholdaction'

and still my database got full 100% and no warning in errorlog...i am confused ??

Also whenever i increase the data segment space (add more space to my database) do i need to reset the thresholds each time ??
0
 
Joe WoodhousePrincipal ConsultantCommented:
Those do look like they should've done something. Are the numbers (pages) correct? Are they calling the correct procedure? ie what does the local sp_thresholdaction do, if there is one? If there isn't, what does the one in sybsystemprocs do?

The other alternative is that it wasn't your default segment that filled - what was the exact error message?

And yes, whenever the database changes size you should recalculate the thresholds based on percentages of the new page count.
0
 
jlsilva01Commented:
About Threshold...
If your sp_thresholdaction (sybsystemprocs.sp_thresholdaction) procedure was empty or not exists, you can't control free space with Threshould.

You can follow this simple example to verify if your environment has the threshould enabled:

use <db_name>
go

sp_helpthreshold
go

segment name                   free pages  last chance? threshold procedure                                                                                                                                                                                                                                            
------------                   ----------- ------------ -------------------                                                                                                                                                                                                                                            
logsegment                          200000            0 sp_thresholdaction                                                                                                                                                                                                                                              
logsegment                          100000            0 sp_thresholdaction                                                                                                                                                                                                                                              
logsegment                          120016            1 sp_thresholdaction                                                                                                                                                                                                                                              
logsegment                          300000            0 sp_thresholdaction    

sp_addthreshold <db_name>, logsegment, 200000
go
sp_addthreshold <db_name>,  logsegment, 100000
go
sp_addthreshold <db_name>,  logsegment, 300000
go

sp_addthreshold <db_name>, logsegment, 300000,  sp_thresholdaction
go

sp_helpthreshold
go

segment name                   free pages  last chance? threshold procedure                                                                                                                                                                                                                                            
------------                   ----------- ------------ -------------------                                                                                                                                                                                                                                            
logsegment                          120016            1 sp_thresholdaction                                                                                                                                                                                                                                              
logsegment                          300000            0 sp_thresholdaction    

use sybsystemprocs
go

sp_helptext sp_thresholdaction
go

drop procedure sp_thresholdaction
go

create procedure sp_thresholdaction (@dbname      varchar(30),
                                     @segmentname varchar(30),
                                     @space_left  int,
                                     @status      int)
as
  declare @dia       varchar(2),
          @mes       varchar(2),
          @ano       varchar(4),
          @hora      varchar(2),
          @minuto    varchar(2),
          @segundo   varchar(2),
          @filename1 varchar(120),    
          @filename  char(120),
          @filepath  varchar(120)    
 
select @filepath = '/u03/backup/logs/'
 
print '******************************************************************************************'
print 'WARNING: ALERT SYBASE - Database log %1! is full! (sp_thresholdaction)' , @dbname
print '******************************************************************************************'
 
select @dia=replicate("0",2-char_length(convert(varchar(2),datepart(dd, getdate()))))+convert(varchar(2),datepart(dd, getdate()))
select @mes=replicate("0",2-char_length(convert(varchar(2),datepart(mm, getdate()))))+convert(varchar(2),datepart(mm, getdate()))
select @ano=convert(varchar(4),datepart(yy, getdate()))
select @hora=replicate("0",2-char_length(convert(varchar(2),datepart(hh, getdate()))))+convert(varchar(2),datepart(hh, getdate()))
select @minuto=replicate("0",2-char_length(convert(varchar(2),datepart(mi, getdate()))))+convert(varchar(2),datepart(mi, getdate()))
select @segundo=replicate("0",2-char_length(convert(varchar(2),datepart(ss, getdate()))))+convert(varchar(2),datepart(ss, getdate()))
select @filename=@filepath+@dbname+"_log_"+@dia+@mes+@ano+@hora+@minuto+@segundo  
select @filename1 = ltrim(rtrim(@filename)) + '.dmp'  
 
dump transaction @dbname to @filename1
 
if @@error <> 0 select 'sp_thresholdaction DBError ' + @dbname
go
grant execute on sp_thresholdaction to public
go

OR

Try to use the commecial solution... Do you know quest spotlight?
This program is very usefull to manager sybase environments.
All questions about this can manage by spotlight.

You can download the trial version at www.quest.com.
0
 
Deepak_FILAuthor Commented:
..
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

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