0

I have a stored procedure which must be constantly running. Occasionally, for a currently unknown reason, it stops. How can I make it automatically restart the SP if this happens? Google searches aren't helping much, as all the results are about restarting the server, instead of the stored procedure. Thanks for any help you can give.

It's a SQLCLR stored procedure, if that makes any difference.

SaintWacko
  • 854
  • 3
  • 14
  • 35
  • 1
    Do you mean a stored proc that once it starts, never finishes - it just keeps on chugging away, or one that runs repeatedly,as soon as one run stops, it restarts again in an endless loop? Because Stored Procedures were never meant to be long-running processes, and that's how this reads. – David Jun 27 '12 at 22:03
  • What do you mean constantly running? That it never terminates? If it never stops and keeps accumulating locks that result in a deadlock, the server may decide to kill it to resolve the deadlock – Panagiotis Kanavos Jun 27 '12 at 22:06
  • @DavidStratton If stored procedures aren't meant to be long running, is there another way to have a SQLCLR assembly constantly running? That's all the SP does, it calls the assembly – SaintWacko Jun 27 '12 at 22:32
  • @PanagiotisKanavos I'm pretty certain it doesn't accumulate any locks, so I don't think that's the problem. – SaintWacko Jun 27 '12 at 22:32
  • Write an app or a service to call the assembly. I would never have even thought of your solution, not sure I'd ever consider implementing it. PS do you know why it's stopping? – Tony Hopkinson Jun 27 '12 at 23:11
  • No, I can't think of any reason why it would. It's supposed to stop if the barcode scanner is disconnected, but that doesn't work for some reason. There shouldn't be any other reason it would stop. – SaintWacko Jun 27 '12 at 23:42
  • Well if there's nothing in the sql server or windows event log, you would have to add logging to find out. Which is another reason, not to do it this way. Could be SQL Server thinking this ain't right, and killing it. – Tony Hopkinson Jun 28 '12 at 22:35
  • @TonyHopkinson I'm trying your idea to just have the scheduler trigger it every few seconds. The interface sets the maximum frequency at once every 10 seconds, but I'm hoping I can do it faster if I create it manually. – SaintWacko Jun 29 '12 at 16:12

3 Answers3

1

You cannot automatically restart it. Such a feature does not exist built-in because it is not really the purpose of procedures.

I'd set up a SQL Server Agent job that executes once every N seconds (N = 1 or 15 or so) and restarts the procedure if needed.

You need a way to detect if it is still running. You could do that by having the sproc hold an exclusive lock on a special marker table. The checking procedure can the read from that table WITH (READPAST). If no rows come back, the sproc is still holding the lock so it must be active.

usr
  • 168,620
  • 35
  • 240
  • 369
0

Write another sp that checks to see if the first one is running and if not kicks it off. Curiousity is crippling me though, what does this thing do?

Tony Hopkinson
  • 20,172
  • 3
  • 31
  • 39
  • I thought about that, but what if something happens to the other one? And the sp is a SQLCLR assembly built from a C# Telnet interface. It connects to a fancy barcode scanner and passes the data from it into a table in the database. – SaintWacko Jun 27 '12 at 22:31
  • Trigger the check procdure from the built in windows or sql server scheduler (agent) services. It doesn't have to run all the time, just check often enough not to miss data. – Tony Hopkinson Jun 27 '12 at 23:08
  • Hm, that's a good idea. I know the frequency it should be getting reads, so I can just set the trigger frequency faster than that. Will that use more processor power, though? – SaintWacko Jun 28 '12 at 15:30
0

As long as you've got a Rube Goldberg machine, may as make it more fun.

Re-write the proc as such:

  1. Pop a message off of a service broker queue. "Why" will become evident later.
  2. Put a message in a service broker queue. Doesn't matter what it is.
  3. Whatever the proc does now.

Now, make the stored procedure the activation stored procedure for the queue, set max_queue_readers = 1 and you're off to the races.

Ben Thul
  • 31,080
  • 4
  • 45
  • 68
  • I didn't even realize what I was doing was a rube goldberg. I'm still fairly new to SQL. Now that I know, I'm kinda trying to find a better way. – SaintWacko Jun 28 '12 at 15:32