Handling Tempdb space full situtation?

Question

Handling Tempdb space full situtation?

rames.net

SSC-Addicted

Points: 468
More actions
July 21, 2008 at 1:24 pm

#130165

How can we handle the situation if the tempdb space was full?
reply with more than 2 diff answers?

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

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

DKG Ten Centuries Points: 1183 More actions · Answer 1

you need to alter the tempdb database in this case(means need to increase the size of DB) from the query analysir, in this situation you might see all the DBs would disappear. But dont worry it would come back once temp db is normal.

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

The real problem is that you need to identify why it is full, first.

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

rames.net (7/21/2008)
reply with more than 2 diff answers?

Why?

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

shahbaz.oradba Right there with Babe Points: 769 More actions · Answer 4

TEMP DB FULL:

first know the reason why it is full then shrink the

temp db,or u can add one more temp file to that from

MMS.:P

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

We also need to know what you think "full" is... how many bytes is your TempDB?

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

Ross McMicken SSCarpal Tunnel Points: 4438 More actions · Answer 6

Is tempdb set to autogrow? If not, that might be the issue as well. One application I support makes liberal use of temp tables. A guy I knew at another company using the same app was complaining about tempdb filling up. It turned out the DBA's at that company restricted tempdb to 50MB, and refused to let it autogrow, giving some specious reason. It took months to get htem to autogrow tempdv, at which point, the issues all went away.

Rebooting the servers on a regular schedule helps as well, as tempdb gets cleaned out during the startup process.

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 7

Heh... 50MB for a TempDB... we start our TempbDB up at 12 Gig!

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

Ross McMicken SSCarpal Tunnel Points: 4438 More actions · Answer 8

The DBA's that specified the 50MB were of the opinion that aproperly designed application doesn't need more tempdb. I was glad I didn't have to deal with them. Our tempdb's typically run 3-6GB.

sgmunson SSC Guru Points: 110640 More actions · Answer 9

Sounds to me like an exam question or homework. Otherwise why ask for 2 different answers. I would suggest that the OP read their course material and find the answers that way... They will likely learn more that way.

Steve

(aka smunson)

:):):)

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)

tosscrosby SSCoach Points: 18045 More actions · Answer 10

Jeff Moden (7/21/2008)
The real problem is that you need to identify why it is full, first.

As Jeff said, this is key. I've seen adhoc queries fill tempdb and bring the system to a halt. The genius that intalled SQL when that incident occurred, put tempdb on the c:\ drive and it ultimately filled the disk. When I assumed the role of DBA, things got moved to appropriate physical partitions with plenty of room for growth and setup appropriate monitoring of the system. There are plenty of processes that utilize tempdb, index rebuilds being but one. I don't think we can give solid advice unless we know what/how it filled up. Restarting the SQL service will recreate tempdb but that's a short term fix as I'm sure whatever filled it up once will execute again, putting you in the exact same situation.

Edit - All my tempdb databases are minimum of 5GB

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 11

Ross McMicken (7/23/2008)
The DBA's that specified the 50MB were of the opinion that aproperly designed application doesn't need more tempdb. I was glad I didn't have to deal with them. Our tempdb's typically run 3-6GB.

On my main system, TempDB is 6 files of 8 GB each.

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

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 12

Ross McMicken (7/23/2008)
The DBA's that specified the 50MB were of the opinion that aproperly designed application doesn't need more tempdb. I was glad I didn't have to deal with them. Our tempdb's typically run 3-6GB.

Wow... That's amazing, Ross... I haven't heard anything like that in a long time... please tell me those DBA's weren't actually certified...

--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: 1004406 More actions · Answer 13

GilaMonster (7/23/2008)
On my main system, TempDB is 6 files of 8 GB each.

Now THAT sounds like a fun system to be on and that the DBA (you, Gail?) might actually have his/her ducks lined up.

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 14

Jeff Moden (7/23/2008)
Wow... That's amazing, Ross... I haven't heard anything like that in a long time... please tell me those DBA's weren't actually certified...

Depends. Do you mean certified as in qualified or certified as in insane?

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