How can a looping stored proc detect a shutdown command in progress?

I have a stored proc that's looping summarizing data from another table.   The problem is, when a shutdown is requested for the Sybase server, the shutdown waits forever on this stored proc.

I don't want to do "shutdown with nowait" because that can potentially corrupt db's.

The sysprocesses table in the master db shows "CHECKPOINT" for the thread running the shutdown command.  Since that can show up for regular checkpoint commands, it can't be used to detect a shutdown.

Is there anyway for my looping stored proc to detect a shutdown is in progress so it can gracefully exit and let the shutdown proceed?

Thanks
Ben Slade
Chevy Chase, MD
LVL 1
BenSladeAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
bretConnect With a Mentor Commented:
I can't think of any particularly easy way, I'm not aware of any global variables, etc. that would show this.

Here are a handful of possible approaches.
1) There is a memory structure called "Resource" that you can examine with dbcc resource(), it has a field rflag1, in which a status bit  R_SHUTDOWN is set when a shutdown begins.  The hard part will be extracting this value in a way that your proc can easily check it

2) I believe the shutdown message is printed to the errorlog fairly early on, so some method (XFS table, perhaps?) of scanning the errorlog might allow your proc to check for that

3) New non-sa connections are not allowed once shutdown has begun, so your proc could run an xp_cmdshell that attempted an ISQL in as a non-sa user and somehow detect the "login not allowed, shutdown in progress" message.  Or perhaps that session could update a counter in some table and exit, then your proc would check if the value had been updated.  (this is probably the most feasible approach).

But I think I would recommend approaching from a different angle.  Have the process that issued the shutdown wait for some time and then try issuing KILL commands against any spids that are still sticking around.   If that doesn't clear them all up after another delay, then SHUTDOWN with NOWAIT.
0
 
BenSladeAuthor Commented:
There is one other way to detect a shutdown in progress, but *only* if the Sybase "MDA" monitoring tables are installed (see http://www.sypron.nl/mda.html ).  I thought of this after reading your response about internal flags being set (sorry I didn't think of it before):

/* Loop forever until a shutdown is detected */
while( 1 = 1)
begin
  waitfor delay "00:00:05"
  if exists (select WaitEventID from master..monProcess where WaitEventID=233) break
end

select * from master..monWaitEventInfo where WaitEventID=233
/* Produces output
** WaitEventID: 233
** WaitClassID: 9
** Description: waiting for active processes during shutdown
*/

Ben Slade
Chevy Chase, MD
0
 
bretCommented:
Thanks for teaching an old dog a new trick.  I doubt you will find a better solution.

Cheers,
-bret
0
All Courses

From novice to tech pro — start learning today.