How do I get sa_server_messages() to return server shutdown messages?

[Breck_Carter]

As far as I can tell, it is impossible to get the sa_server_messages() procedure to return server shutdown messages because (a) the server is shutting down after all, (b) there is no DatabaseStop event where you could CALL sa_server_messages() and (c) the message list is reset at startup.

Point (a) is just your everyday Catch 22.

As for (b), I would cheerfully [politically-incorrect-rhetoric] kill [/politically-incorrect-rhetoric] for a DatabaseStop ServerStop event, but I won't hold my breath 🙂

Point (c) may be an opportunity, however... IMO the server shutdown messages should be preserved at shutdown for future calls to sa_server_messages() after startup.

The reason is this: Server shutdown events are among the most interesting of all events... especially when Bad Things Happen.

---

Here's an example: When a version 11.0.1.2276 dbeng11.exe is started with the -z option, it writes quite a few messages at shutdown time, none of which (AFAIK) are available via sa_server_messages():

I. 01/21 07:35:23. Database server shutdown requested via server console
I. 01/21 07:35:23. Disallowing new connections
I. 01/21 07:35:23. Shutting down databases
E. 01/21 07:35:23. TCP/IP link, function WSARecvFrom, error code 995
E. 01/21 07:35:23. TCP/IP link, function WSARecvFrom, error code 995
I. 01/21 07:35:23. Starting checkpoint of "ddd11" (ddd11.db) at Fri Jan 21 2011 07:35
I. 01/21 07:35:23. Finished checkpoint of "ddd11" (ddd11.db) at Fri Jan 21 2011 07:35
I. 01/21 07:35:24. Trying to stop SharedMemory link ...
I. 01/21 07:35:24.     SharedMemory link stopped
I. 01/21 07:35:24. Trying to stop TCPIP link ...
I. 01/21 07:35:24.     TCPIP link stopped
I. 01/21 07:35:24. Completing server shutdown
I. 01/21 07:35:24. Database server stopped at Fri Jan 21 2011 07:35

[graeme_perrow]

You could use the -o switch to send the messages to a file, and then parse the file. Certainly not ideal if you want to be notified when the server is shutting down, but if you're trying to do a post-mortem after the fact, it would work.

[former_SQLA_member1694868]

Is there a way to turn this switch on while the server is running?

[VolkerBarth]

@Siger: Yep, "CALL sa_server_option('ConsoleLogFile', 'C:MyLog.tst') - specifying an empty path stops logging.

[Breck_Carter]

@Graeme: The question was for completeness... if one is recording the console messages to a database table, one would like shutdown messages to be included. Then -o together with -on and -oe could be used as fallback for emergencies where the database table is unavailable and you want to see "what just happened". Otherwise, the -o and -oe files are throwaways, hence -on instead of -os.

[Breck_Carter]

@Graeme: Being notified that a server is shutting down implies a great deal of magic, does it not? ...asking for that is way above my pay grade 🙂

[Former Member]

Either a local host cron/at job can test if the db service is running, and the same job can also fire a test query/connection to the db. If either return non-zero then a page can be sent with the message indicating which test failed. Another means that would introduce a free product is Nagios monitor system, this would support notification of the server failure as well. I realize not an answer/solution to an internal DB job/event, but is the frequent implementation at most sites.