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
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 126.96.36.1996 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
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.
answered 21 Jan '11, 13:24
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.
answered 28 Jan '11, 20:24