Stalled Process in 2005

  • Andrew Gothard-467944

    Hall of Fame

    Points: 3908

    Hi,

    problem is as follows; have a SSIS package performing an ETL for a reporting database. This has been running fine for a couple of months until earlier this week (no changes made to SSIS or db), but the process is now 'stalling' at a point in the process near the end where a sp is run to update a customer fact (aye, Fact - unusual, I know).

    The sp should load data into a table variable, based on a view on the staging tables, and update the Customer Fact based on this data. The loading of the Table Var is OK - and I can run that OK in QA. However, the update of the customer fact just sits there in a huff. No other process is accessing the table.

    The dB is set up so files Autogrow, there's space left in dB and plenty of discspace available (same with TempDB), so don't think it's a space issue.

    The process is runnning on a lightly used 4 Processor box, 4Gb SQLServer 2005 Standard SP2. I've attached output for the process from Erland Sommarskog's aba_lockinfo and sysprocesses - any help or suggestions gratefully received

    cheers

    Andrew

  • G.R.Prithiviraj Kulasingham

    SSCarpal Tunnel

    Points: 4104

    This could even be a data issue. IS there any branching, looping conditions you have?

    Is your package starts and after processing something it fails (without error) or goes into an unending loop?

    You could possibily do a logging. so that you can identify where it stops. Then you can check why it stops there.

    Cheers,
    Prithiviraj Kulasingham

    http://preethiviraj.blogspot.com/

  • Andrew Gothard-467944

    Hall of Fame

    Points: 3908

    Branching or Looping? Perish the thought :w00t:

    Very straightforward update:

    DECLARE @tblHolding TABLE

    (

    OutcomeID INT,

    Event_Date DATETIME,

    EVENT_TIME DATETIME,

    CustomerID NVARCHAR(40),

    RecordPenetrated BIT

    )

    INSERT @tblHolding

    SELECT

    vwNonContactOutcomesNew.OutcomeID,

    vwNonContactOutcomesNew.Event_Date,

    vwNonContactOutcomesNew.Event_Time,

    vwNonContactOutcomesNew.CustomerID,

    vwNonContactOutcomesNew.RecordPenetrated

    FROM

    vwNonContactOutcomesNew

    UPDATE dbo.CustomerFact

    SET

    OutcomeID = HoldingTable.OutcomeID,

    LastContactDate = HoldingTable.Event_Date,

    LastContactTime = HoldingTable.Event_Time,

    RecordPenetrated = HoldingTable.RecordPenetrated

    FROM

    dbo.CustomerFact

    INNER JOIN

    @tblHolding AS HoldingTable

    ON dbo.CustomerFact.PhoenixCustomerID = HoldingTable.CustomerID

  • Andrew Gothard-467944

    Hall of Fame

    Points: 3908

    Thanks to those who has a look at this one - just in case anyone's wondering I've sussed it (ish).

    Basically - looks as if it may have been some kind of contention caused by trying to update the covering indexes.

    I changed the process to drop all except the clustered PK index, do the update and then set up the covering indexes once it's complete. The gaffer was very impressed with this amazing piece of lateral thought and investigative work. "How did you work that out?!"

    "Errrrr - I'd tried everything else other than a raindance. That was next ..... "

  • Jeffrey Williams

    SSC Guru

    Points: 88607

    Instead of dropping/recreating the indexes - look at just disabling the indexes and rebuilding them after the process has completed.

    ALTER INDEX index ON table DISABLE; --disables the index

    You still have to loop over all non-clustered indexes, but it is simple. Now, once everything is done - you can do this:

    ALTER INDEX ALL ON table REBUILD WITH (SORT_IN_TEMPDB = ON);

    If you have Enterprise Edition, you can even rebuild the indexes online but I don't think that would necessary for this process.

    Jeffrey Williams
    Problems are opportunities brilliantly disguised as insurmountable obstacles.

    How to post questions to get better answers faster
    Managing Transaction Logs

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

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