July 5, 2017 at 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
July 5, 2017 at 1:35 pm
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
July 5, 2017 at 1:39 pm
coolchaitu - Wednesday, July 5, 2017 1:21 PMGood 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.)
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
July 5, 2017 at 6:15 pm
GilaMonster - Wednesday, July 5, 2017 1:35 PMWhat 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?
July 6, 2017 at 5:48 am
coolchaitu - Wednesday, July 5, 2017 6:15 PMGilaMonster - Wednesday, July 5, 2017 1:35 PMWhat 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?
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply