3 Replies Latest reply on Jan 15, 2009 7:37 AM by Newsgroup_User

    How to terminate a running stored procedure

    minli98 Level 1
      Hi,

      I am starting to use stored procedures instead of doing cfquery, but I am nervous about procedures that take too long to run. Is there a way to terminate running procedures so that there is no risk of errant procedures crashing the sql server? What is the safest way of testing these procedures?

      Thank you.

      Min
        • 1. Re: How to terminate a running stored procedure
          Dan Bracuk Level 5
          Using cfquery presents the same problem as using stored procedures. What happens in both cases is.

          Cold Fusion asks the db to do something.
          The db starts doing it.
          When the db finishes, it sends something back to Cold Fusion.

          If the Cold Fusion template times out, the db does not stop what it was doing. If you want to stop it, you have to do so at the db level, not on Cold Fusion.

          The way we do it on our data warehouse (redrick running on unix) is to schedule a job to run every 5 minutes. This job checks for any query being run by Cold Fusion that has been going longer than 120 seconds. If it finds any, it stops them and sends an email.
          • 2. Re: How to terminate a running stored procedure
            minli98 Level 1
            Hi Dan,

            Thanks for the comment. I am not so worried with running cfquery because I usually don't do any looping. Pretty much all cfquery I do is of select/update/insert variety. However, by migrating to stored procedures, I am hoping to move more of the data manipulation from coldfusion to t-sql.

            I forgot to mention that I am on a shared hosting plan. Am I wrong to think that I don't have the access to kill errant sql jobs?

            Thanks,
            Min
            • 3. Re: How to terminate a running stored procedure
              Level 7
              minli98 wrote:
              >
              > I forgot to mention that I am on a shared hosting plan. Am I wrong to think
              > that I don't have the access to kill errant sql jobs?
              >


              Very likely, but it would depend on your exact contract with your
              hosting provider.

              Also, if your provider is worth their contracts, they probably already
              have processes in place to kill errant database processing. Otherwise
              it would be very easy for users to kill each other affecting the hosting
              provider's reputation with their clients.