ETL job consuming all tempdb space and failing

  • coolchaitu

    SSChampion

    Points: 10267

    Good morning experts,

    There is a ETL job that has been running fine since 2014. The job did not get any modifications. Average run time of job is less than 2 minutes. The job ran yesterday for quarterly end reporting, used up all the tempdb space and failed. We expanded the tempdb drive by 50 GB and re-ran the job, it again used up all tempdb space and failed again. There is no other activity on the instance. It is using up all the tempdb space and failing with insufficient disk space in tempdb error. How to fix this issue? Could you please help

  • Gail Shaw

    SSC Guru

    Points: 1004446

    What changed since the last time it ran successfully?

    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
  • Phil Parkin

    SSC Guru

    Points: 243689

    coolchaitu - Wednesday, July 5, 2017 1:21 PM

    Good morning experts,

    There is a ETL job that has been running fine since 2014. The job did not get any modifications. Average run time of job is less than 2 minutes. The job ran yesterday for quarterly end reporting, used up all the tempdb space and failed. We expanded the tempdb drive by 50 GB and re-ran the job, it again used up all tempdb space and failed again. There is no other activity on the instance. It is using up all the tempdb space and failing with insufficient disk space in tempdb error. How to fix this issue? Could you please help

    Just because the job is unchanged does not mean that
    a) The data it is processing is unchanged
    b) The software and operating system it is running on is unchanged
    c) The hardware is unchanged
    d) I could go on
    The point is, something has changed which has affected things, and you need to find out what it is.
    Start by running the job in a dev environment and seeing whether it fails there, perhaps. Or running on a backup which previously ran fine to see whether it still runs OK (if yes, there's probably a data-related problem.)

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

  • coolchaitu

    SSChampion

    Points: 10267

    GilaMonster - Wednesday, July 5, 2017 1:35 PM

    What changed since the last time it ran successfully?

    Hi Gail
    Nothing has changed. I have a doubt. Can fragmentation in a user database cause tempdb to grow full? Why is ETL job using tempdb?

  • Phil Parkin

    SSC Guru

    Points: 243689

    coolchaitu - Wednesday, July 5, 2017 6:15 PM

    GilaMonster - Wednesday, July 5, 2017 1:35 PM

    What changed since the last time it ran successfully?

    Hi Gail
    Nothing has changed. I have a doubt. Can fragmentation in a user database cause tempdb to grow full? Why is ETL job using tempdb?

    If nothing has changed, the job will not fail. That's one of the good things about computers.
    Unless you tell us what the ETL job is doing, how can we possibly guess whether it should be using Tempdb?

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

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

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