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

asked 21 Jan '11, 13:16

Breck%20Carter's gravatar image

Breck Carter
32.5k5397241050
accept rate: 20%

edited 22 Jan '11, 09:30


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.

permanent link

answered 21 Jan '11, 13:24

Graeme%20Perrow's gravatar image

Graeme Perrow
9.6k379124
accept rate: 54%

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

(21 Jan '11, 15:13) Siger Matt
2

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

(21 Jan '11, 16:28) Volker Barth

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

(22 Jan '11, 09:25) 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 :)

(22 Jan '11, 09:28) Breck Carter

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.

permanent link

answered 28 Jan '11, 20:24

Cindy's gravatar image

Cindy
21111
accept rate: 0%

Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×106

question asked: 21 Jan '11, 13:16

question was seen: 2,045 times

last updated: 28 Jan '11, 20:24