August 7, 2008 at 5:49 am
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
August 7, 2008 at 6:56 am
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/
August 7, 2008 at 7:19 am
Branching or Looping? Perish the thought :w00t:
Very straightforward update:
DECLARE @tblHolding TABLE
(
OutcomeIDINT,
Event_DateDATETIME,
EVENT_TIMEDATETIME,
CustomerIDNVARCHAR(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
August 10, 2008 at 6:57 am
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 ..... "
August 10, 2008 at 10:10 am
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
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply