Tempdb Size increases and acquires the complete disk space

Question

Post reply

Tempdb Size increases and acquires the complete disk space

Manuni Shah

SSC-Addicted

Points: 425
More actions
March 7, 2011 at 4:53 am

#388987

The issue goes as follows,
Tempdb size generally remains around 3GB. One day it starts increasing and acquires the whole of the empty disk space of 29 GB within 15 hours.
How can we avoid such kind of situations from happening in future?
Any specific reason for such thing to happen?
Also the .LDF file is as of now (after restart of SQL Service and tempdb size around 2.5 GB) is around 1.4 GB though the RECOVERY model is set to SIMPLE. Why is it so ?
Early reply would be helpful.
Thanks in advance,
Regards
Manuni

Viewing 15 posts - 1 through 14 (of 14 total)

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

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 1

DId you have any heavy migration in recent past ? or any sql level code changes ?

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

Manuni Shah SSC-Addicted Points: 425 More actions · Answer 2

Manuni Shah

SSC-Addicted

Points: 425

March 7, 2011 at 10:01 pm

#1295695

no, nothing of that sort happened

hemdbya SSChasing Mays Points: 647 More actions · Answer 3

->Check iI there any daily EOD process running which creates the n no. of temp tables and inserting a data into your prod.

->have you recently rebuild your all indexes?

हेमडब्या

M&M SSC-Insane Points: 21700 More actions · Answer 4

How regularly are you seeing this pattern. Based on that, we could give you some better inputs.

Have you tried to find out if there is any query running on the instance at the time the tempdb grows.

M&M

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 5

THis link can help you to undestand the working of TempDB http://technet.microsoft.com/en-us/library/cc966545.aspx#EDAA

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

Manuni Shah SSC-Addicted Points: 425 More actions · Answer 6

this was the first time it has happened on our Production Servers.

Manuni Shah SSC-Addicted Points: 425 More actions · Answer 7

indexes and stats are rebuilt /updated every Sunday. Job has been scheduled for this. This issue happened on Thursday.

AppSup_dba SSCrazy Points: 2215 More actions · Answer 8

Hi Manuni,

Can you figure out what all query/jobs were executing when the TEMDB went full? This will help you in locating the cause of the issue.

Cheers

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 9

Manuni Shah (3/7/2011)
The issue goes as follows,
Tempdb size generally remains around 3GB. One day it starts increasing and acquires the whole of the empty disk space of 29 GB within 15 hours.
How can we avoid such kind of situations from happening in future?
Any specific reason for such thing to happen?
Also the .LDF file is as of now (after restart of SQL Service and tempdb size around 2.5 GB) is around 1.4 GB though the RECOVERY model is set to SIMPLE. Why is it so ?
Early reply would be helpful.
Thanks in advance,
Regards
Manuni

There's not much you can do to prevent this from happening if users or developers have ad hoc access to the production server unless you set up some sort of email alert on TempDB growth to catch them red-handed. Chances are someone wrote a "special" a hoc reporting query with a many-to-many join (I call them "accidental Cross Joins" because they have the same effect). The working tables necessary to produce the result will fill up TempDB if it needs it.

Heh... it must have been really bad code... normally such a problem with eat the face off of TempDB in just a couple of hours instead of 15. 😛

--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)

mazzz SSCertifiable Points: 6139 More actions · Answer 10

I had a similar issue a while back.

You could set up monitoring like in this blog post here

You could maybe somehow set it up to alert when the difference between allocated and unallocated objects becomes bigger than X value.

As Jeff says there's probably a hefty query being run on occasion that is using a lot of internal objects in tempdb (that was the case in my scenario too)

------------------------------------------------------------------------
Bite-sized fiction (with added teeth) [/url]

mazzz SSCertifiable Points: 6139 More actions · Answer 11

Jeff Moden (3/9/2011)
Heh... it must have been really bad code... normally such a problem with eat the face off of TempDB in just a couple of hours instead of 15. 😛

Heh, in my scenario a query chewed up 30GB of tempdb in under ten minutes. Was that efficient bad code...? 🙂

------------------------------------------------------------------------
Bite-sized fiction (with added teeth) [/url]

tosscrosby SSCoach Points: 18045 More actions · Answer 12

Our end users (supposedly a high end user who had experience) who ran an adhoc query in PeopleSoft. Totally filled tempdb and brought SQL to it's knees, due to improper joins. Took her access away and forced her to open tickets for support/assistance in creating queries.

-- You can't be late until you show up.

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 13

mazzz (3/9/2011)
Jeff Moden (3/9/2011)
Heh... it must have been really bad code... normally such a problem with eat the face off of TempDB in just a couple of hours instead of 15. 😛
Heh, in my scenario a query chewed up 30GB of tempdb in under ten minutes. Was that efficient bad code...? 🙂

BWAA-HAA!!! Yeah... "efficient bad code" is a good word for that. 😀

--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: 1004339 More actions · Answer 14

tosscrosby (3/9/2011)
Our end users (supposedly a high end user who had experience) who ran an adhoc query in PeopleSoft. Totally filled tempdb and brought SQL to it's knees, due to improper joins. Took her access away and forced her to open tickets for support/assistance in creating queries.

I have mixed feelings about something like that. Yep, I agree... my first reaction is to remove access. Let's consider the ramifications of that.

1. We just made her job a whole lot longer and more difficult to do.

2. We are now the ones doing her job which has its own major responsibility ramifications.

3. Resentment has been built between users and the people who removed the privs.

So there has to be a followup step so we can return this person to being useful. That would be to gently and kindly educate the person as to what they've done and how to check an execution plan to make sure it doesn't happen again.

Without that step, all one has done is cover a stab wound with a bandaid. 😛

--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)