Post reply

Strange behavior with update in SP

  • September 25, 2008 at 4:22 am

    #123747

    I have the following strange situation:

    During a datawarehouse refresh, I update a large table (16 million rows). This update statement is imbedded in a stored procedure, together with several other statements. This job is scheduled by SQLAgent

    For some reason, SQL hangs during this update (starting last week). No SQL errormessage, no diskspace issue, no OS events. No other processes are running. CheckDb is not reporting any problems. The update just stops (process is still there). The stored procedure was not modified. A restart of the server did not solve this problem.

    If I stop the job and restart at the step which is causing this problem, it works fine. So I assume the job owner is also not causing this issue. But if (not sure) it's memory, why is there no error message?

    If I copy/past the update statement and run this seperately, everything works fine.

    Version is 9.00.3152 Enterprise 32bit, 5120MB (AWE) memory, uses all 4 CPU

    Anny ideas? suggestions?

    Wilfred
    The best things in life are the simple things

  • September 25, 2008 at 5:04 am

    #876889

    When it is "stuck", what state is the update in? Check it with sp_who2.

    Most stuck processes are either blocked by another process or SUSPENDED waiting on a resource. If it is blocked by another process, it may be a process earlier in your warehouse load that did not let go of a lock in your database. If the process is suspended, it could be waiting on memory, CPU, disks, etc. This also could be caused by some resource pressure from something earlier in the process.

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

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