what is causing tempdb to become full

Question

what is causing tempdb to become full

coolchaitu

SSChampion

Points: 10332
More actions
November 13, 2017 at 3:16 am

#340442

Good Morning Experts
Application team is doing something that is causing tempdb to become full. We shrank tempdb log file. Again tempdb became full. We want to know what is causing tempdb to become full.

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

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

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 1

I just typed the title of your topic into my favourite search engine, and it provided lots of results that I think would answer your question. Try it yourself.

John

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 2

Except for an old bug for log file growth being set to 4,000MB which, according to most, has been repaired, that answer as to what is causing TempDB to grow in a crazy fashion is simple... someone is running some crap code.

Ok, so how do you find it? You have to detect the growth and then capture the query that's causing it. One method to do that can be found in the following article.

https://www.mssqltips.com/sqlservertip/3276/sql-server-alert-for-tempdb-growing-out-of-control/

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 3

Also Kiran, to John Mitchell's point, you ask a whole lot of questions on many forums that have simple and quick answers if you spend just a little time and effort on your part. Using the title to your post, it took only 4 minutes to find the article I provided the link to. It's not like you're a beginner at SQL... you first became a member of SSC more than 7 years ago.

This isn't meant as a slam... it's a suggestion that has helped me learn a whole lot and helps me avoid panic situations. Spend some time learning the product and how to use Google to help (but don't let it become a crutch like what you currently use forums for). When problems like this one occur, it'll take you a whole lot less time to come up with a solution and, if you study the code causing the problem (usually code with an accidental cross join in the form of a many-to-many inner join because someone doesn't know the data), you'll be able to prevent such things in the future.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

coolchaitu SSChampion Points: 10332 More actions · Answer 4

Jeff Moden - Monday, November 13, 2017 6:14 AM
Also Kiran, to John Mitchell's point, you ask a whole lot of questions on many forums that have simple and quick answers if you spend just a little time and effort on your part. Using the title to your post, it took only 4 minutes to find the article I provided the link to. It's not like you're a beginner at SQL... you first became a member of SSC more than 7 years ago.
This isn't meant as a slam... it's a suggestion that has helped me learn a whole lot and helps me avoid panic situations. Spend some time learning the product and how to use Google to help (but don't let it become a crutch like what you currently use forums for). When problems like this one occur, it'll take you a whole lot less time to come up with a solution and, if you study the code causing the problem (usually code with an accidental cross join in the form of a many-to-many inner join because someone doesn't know the data), you'll be able to prevent such things in the future.

Thank you Jeff. Using Google actually does not help because anybody can write anything there. It is not authentic. But forums like SQLServerCentral have experts like you who are authentic and thats why i ask questions here( thats what the purpose of this forum stands). I became a member 7 years ago, but I did not actually use it properly until sometime back. Sorry if I hurt you unknowingly.

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 5

coolchaitu - Monday, November 13, 2017 10:05 AM
Jeff Moden - Monday, November 13, 2017 6:14 AM
Also Kiran, to John Mitchell's point, you ask a whole lot of questions on many forums that have simple and quick answers if you spend just a little time and effort on your part. Using the title to your post, it took only 4 minutes to find the article I provided the link to. It's not like you're a beginner at SQL... you first became a member of SSC more than 7 years ago.
This isn't meant as a slam... it's a suggestion that has helped me learn a whole lot and helps me avoid panic situations. Spend some time learning the product and how to use Google to help (but don't let it become a crutch like what you currently use forums for). When problems like this one occur, it'll take you a whole lot less time to come up with a solution and, if you study the code causing the problem (usually code with an accidental cross join in the form of a many-to-many inner join because someone doesn't know the data), you'll be able to prevent such things in the future.
Thank you Jeff. Using Google actually does not help because anybody can write anything there. It is not authentic. But forums like SQLServerCentral have experts like you who are authentic and thats why i ask questions here( thats what the purpose of this forum stands). I became a member 7 years ago, but I did not actually use it properly until sometime back. Sorry if I hurt you unknowingly.

Nope... thank you for your concern and kind words but there was no hurt involved here. And understood on the quality thing. I agree that the internet is frequently a well paved on-ramp to a dirt road.

Shifting gears to the subject at hand, did you read the article at the link I provided? I'm thinking that's what you need to do to find the code that's causing your problem.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)