Post reply

A small procedure blocking for days

  • March 6, 2012 at 4:34 am

    #254187

    Hi,

    I have a small stored procedure which takes only 30 seconds when i execute manually.

    So I planified a job to execute it. But sometimes(not always) it takes some days and never finishes, untill i kill it.

    In a few seconds it's killed and when i re-execute manually, i have the result in 30 seconds...

    I really don't understand the reason.

    the procedure looks like :

    CREATE procedure [dbo].[PROCEDURE]

    AS

    SET NOCOUNT on

    declare @myGuid UNIQUEIDENTIFIER, @error int

    insert into x

    select * from y

    delete from y

    ...

    Does someone have an idea ?

    Thanks in advance.

  • March 6, 2012 at 5:10 am

    #1455211

    Can you post the actual procedure?

    When it runs forever, what's the query waiting for? (check sys.dm_exec_requests

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply