Which errors should be logged that are currently just sent to client?

[former_member188958]

ASE follows these general rules regarding when to send error messages to the errorlog
1) Messages with a severity 19 or higher are sent to the errorlog.
2) Messages with a severity lower than 19 are sent to the errorlog when encountered by system processes (as there is no client to send them to).
3) Messages that have had sp_altermessage <error>, "with_log", "true" run on them are sent to the errorlog.

Many of the "out of resource" type of message have a severity level of 17, so by default are only reported to the client application.  I've often heard from database administrators that they were surprised that certain messages were not being reported in the log.  Common examples include 1105 (out of space on segment), 1204 (out of locks), 701 (insufficient procedure cache memory).  Documentation on severity levels is at http://infocenter.sybase.com/help/topic/com.sybase.infocenter.dc31654.1570/html/sag1/sag1568.htm

Note: just to complicate the issue slightly, the Error Messages guide and the master..sysmessages table document a severity level but occasionally have been incorrect (1204 is raised with severity 17 is still shown in sysmessages as being 19, for instance).  Some messages may be raised with different severity levels depending where in the code they are raised.  The severity level value in the sysmessages table is just documentary; the severity level hardcoded in the ASE sourcecode is what actually determines the severity level used.

The question I'd like to put out there is "What messages have you encountered that you thought should go to the ASE errorlog by default that weren't going there?"   I'll put a summary of the answers in a feature request change request (CR).

-bret

[former_member188958]

Severity 17 is used for most errors related to resource shortages that an SA would probably

like to see sent to the errorlog.   Here is a  query that an SA can use to generate a script

that will turn “with_log” on for every severity 17 error (you can and should go through it and comment out

any error that you  don’t actually want to see in the errorlog, as not all severity 17 have to do with adjustable resources).

select

                "/*"

+             description

+             "*/"

+             char(10)

+             "exec sp_altermessage "

+             str(error,8,0)

+             ", 'with_log', true"

+             char(10)

+             "go"

FROM

                master..sysmessages

WHERE

                severity = 17

go

Sample output follows, just add "--" in front of exec to comment out any errors

you don't want logging turned on for.

[...]

/*There is insufficient memory to allocate the structures needed to set local
switches. Contact your System Administrator.*/

exec sp_altermessage     12881,  'with_log', true

go

/*Couldn't find an available partition descriptor. Raise the value of the
configuration parameter 'number of open partitions' for more partition
descriptors.*/

exec sp_altermessage     14126, 'with_log', true

go

/*Could not allocate a local hash table for '%S_INDINFO'. Raise the value of
configuration parameter 'procedure cache size'.*/

exec sp_altermessage     14131, 'with_log', true

go

[...]

[former_member188958]

As of 16.0 SP03 PL04, the sp_altermessage procedure has been enhanced to allow setting the "with_log" property on all messages of a particular severity level.

Example

Specifies that error messages of severity level 16 are included in the error log:

sp_altermessage -16, "with_log", true
go
3611 Messages altered.
(return status = 0)

[sladebe]

Wow. I'm surprised that a lot of these messages were deemed not important enough to write to the errorlog. At my site, we modified error 701 ('out of procedure cache') to show up in the errorlog, and we have monitoring scripts periodically run sp_monitorconfig 'all' and alarm on that output, but it looks like there's more to do here.