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 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 |
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. |