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?

Ben Slade
Chevy Chase, MD
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
BenSladeAuthor Commented:
There is one other way to detect a shutdown in progress, but *only* if the Sybase "MDA" monitoring tables are installed (see ).  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)
  waitfor delay "00:00:05"
  if exists (select WaitEventID from master..monProcess where WaitEventID=233) break

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
Thanks for teaching an old dog a new trick.  I doubt you will find a better solution.

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Sybase Database

From novice to tech pro — start learning today.