SQL Server Performance

What happens to SP when VB times out?

Discussion in 'Performance Tuning for DBAs' started by hominamad, May 21, 2004.

  1. hominamad Member

    We have a typical web application set up. ASP, with COM middle tier, and SQL Server 2000 database. Here's the question. What happens in the scenario where a stored procedure takes a long time to finish, and a VBScript timeout occurs? Even though the database-web connection is broken, will the stored procedure finish executing behind the scenes? If not, how does it know to stop?

    Are there certain environmental parametes which could affect the behavior in this situation?

    Thanks!

    ~H
  2. gaurav_bindlish New Member

    The stored procedure will stop executing and if it was a part og transaction, all the changes will be rolled back. When a connection is broken, I guess Open Data Services component is notified at the server which does the clean up.

    Refer to Inside SQL Server 2000 for more details

    Gaurav
    Moderator
    Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard

    The views expressed here are those of the author and no one else. There are no warranties as to the reliability or accuracy of anything presented here.
  3. Luis Martin Moderator

  4. hominamad Member

    quote:Originally posted by LuisMartin

    Check:
    http://support.microsoft.com/default.aspx?scid=kb;EN-US;287499

    Thanks - but that article just explains how to set the timeout value. Also, we're not using COM transactions. I want to know what happens to stored procedure execution when called from VB via ADO and the VB connection times out. Thanks!

    ~H
  5. Argyle New Member

    As mentioned if the connection is broken due to a VB timeout any transactions will be rollbacked. If you are not using transactions and have say multiple update statments in the SP then you could end up with 3 out of 5 updates being performed. If you have multiple updates/inserts/deletes always use transactions. If it's a single update statment and the connection is broken and the update wasn't fisnished it will also be rollbacked.
  6. hominamad Member

    quote:Originally posted by Argyle

    As mentioned if the connection is broken due to a VB timeout any transactions will be rollbacked. If you are not using transactions and have say multiple update statments in the SP then you could end up with 3 out of 5 updates being performed. If you have multiple updates/inserts/deletes always use transactions. If it's a single update statment and the connection is broken and the update wasn't fisnished it will also be rollbacked.

    Thanks for the reply. We have a lot of situations where VB calls a single stored procedure which then triggers off a series of steps all on the database. Almost all of the logic is in stored procedures and it doesn't come back to the VB until the process is complete.

    These procs contain many inserts and updates - done in cursors, etc. In this case, if the VB times out, it sounds like you're saying that whatever process was going on in the database at that time will continue, but further calls will not execute. Am I understanding this correctly?

    Thanks,
    ~H
  7. satya Moderator

    Yes, if those updates managed using transactions then only uncommitted will be rolled back.

    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.

Share This Page