SQL Job used to work - now fails

Posted on 2011-05-05
Last Modified: 2012-05-11
i have a job - that has been working for a year now - it imports a file into a table then runs an update statement that goes against MULTIPLE tables...

the import table has now become larger (>100K records) - and now i'm getting The package execution returned DTSER_FAILURE (1) after about 8 hours...

any idea?
Question by:quigleyryan
    LVL 142

    Expert Comment

    by:Guy Hengel [angelIII / a3]
    that usually means you are missing indexes for the UPDATEs ...
    having a index on the joining fields is the plan to have.
    LVL 5

    Expert Comment

    If the process used to be fast but has got (gradually or suddenly) slow then yes indexes could be a problem (because the updates could run so slow that something somewhere times out.  However, you can perform updates without indexes just fine, and 100k rows isn't huge.

    You could try splitting the imported data into 3 or 4 different files and loading each individually to see if the volume is causing the problem or whether you have some other issue.
    LVL 9

    Expert Comment

    check your windows event log for error details
    LVL 35

    Expert Comment

    by:David Todd

    Author Comment

    radcaesar - event log only says job failed, eventid = 12291

       [ Qualifiers]  16385
    LVL 35

    Expert Comment

    by:David Todd

    Maybe you need to test the package - DTS or SSIS and see if you can find which step fails.

    Given you are on SQL 2008, why not wrap etl procedures in a begin try end try - begin catch end catch pair, and simply log everything that goes through the catch block - I call a procedure to this for me adding the name of the procedure I'm calling from, and a note which is generally empty.

    Then its just a matter of looking at the log to see what went wrong where.

    LVL 21

    Accepted Solution

    Please optimize SQL code in SSIS.

    Featured Post

    IT, Stop Being Called Into Every Meeting

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    Join & Write a Comment

    Audit has been really one of the more interesting, most useful, yet difficult to maintain topics in the history of SQL Server. In earlier versions of SQL people had very few options for auditing in SQL Server. It typically meant using SQL Trace …
    This is basically a blog post I wrote recently. I've found that SARGability is poorly understood, and since many people don't read blogs, I figured I'd post it here as an article. SARGable is an adjective in SQL that means that an item can be fou…
    This video is in connection to the article "The case of a missing mobile phone (". It will help one to understand clearly the steps to track a lost android phone.
    Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

    754 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    25 Experts available now in Live!

    Get 1:1 Help Now