SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


what is causing tempdb to become full


what is causing tempdb to become full

Author
Message
coolchaitu
coolchaitu
SSCrazy Eights
SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)

Group: General Forum Members
Points: 8263 Visits: 1627

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.


John Mitchell-245523
John Mitchell-245523
SSC Guru
SSC Guru (132K reputation)SSC Guru (132K reputation)SSC Guru (132K reputation)SSC Guru (132K reputation)SSC Guru (132K reputation)SSC Guru (132K reputation)SSC Guru (132K reputation)SSC Guru (132K reputation)

Group: General Forum Members
Points: 132001 Visits: 19217
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
Jeff Moden
SSC Guru
SSC Guru (871K reputation)SSC Guru (871K reputation)SSC Guru (871K reputation)SSC Guru (871K reputation)SSC Guru (871K reputation)SSC Guru (871K reputation)SSC Guru (871K reputation)SSC Guru (871K reputation)

Group: General Forum Members
Points: 871241 Visits: 47463
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (871K reputation)SSC Guru (871K reputation)SSC Guru (871K reputation)SSC Guru (871K reputation)SSC Guru (871K reputation)SSC Guru (871K reputation)SSC Guru (871K reputation)SSC Guru (871K reputation)

Group: General Forum Members
Points: 871241 Visits: 47463
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
coolchaitu
coolchaitu
SSCrazy Eights
SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)

Group: General Forum Members
Points: 8263 Visits: 1627
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
Jeff Moden
SSC Guru
SSC Guru (871K reputation)SSC Guru (871K reputation)SSC Guru (871K reputation)SSC Guru (871K reputation)SSC Guru (871K reputation)SSC Guru (871K reputation)SSC Guru (871K reputation)SSC Guru (871K reputation)

Group: General Forum Members
Points: 871241 Visits: 47463
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum








































































































































































SQLServerCentral


Search