TEmp DB is becoming huge

Question

TEmp DB is becoming huge

Hammad khan

Mr or Mrs. 500

Points: 552
More actions
March 13, 2007 at 11:53 am

#177124

I have database runiing on sql 2000/sp4 .The size of the databse is 10G.
Temp DB is becoming very huge upto 40 GB. I find out the temp DB is not doing checkpoint.
How can i make Temp db to do check point. As a temp fix i create the sql job which
is doing dbcc srink file every 5 minutes.How can i make temp db to do check point??

Thanks
Hammad

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

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

Farhad-386799 Say Hey Kid Points: 681 More actions · Answer 1

Temp dm should be in simple mode-- Why is it growing this fast? If you application requires huge temp space then you should partition your tempdatabase etc...

Hope this helps

Marvin D SSCarpal Tunnel Points: 4058 More actions · Answer 2

Correct me if I'm wrong, but isn't the tempDB only resized at start up? Your queries are probably doing a bunch of sorts which is filling up your tempdb.

If it is the tempdb log file that is filling up then change it to the simple recovery model and that should help.

read the following article for the highlights of the tempdb

http://www.microsoft.com/technet/prodtechnol/sql/2005/workingwithtempdb.mspx

Marvin Dillard
Senior Consultant
Claraview Inc

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

As some have suggested, step 1 would be to ensure the Recovery Mode on TempDB is set to SIMPLE.

Step 2 would be to rewrite your code so it doesn't use the pot wad of cursors I think you're probably using... the following references are mostly for SS2005 but a good abount of the information applies to 2k, as well.

http://msdn2.microsoft.com/en-us/library/ms345368.aspx

http://msdn2.microsoft.com/en-us/library/ms190768.aspx

http://msdn2.microsoft.com/en-us/library/ms175527.aspx

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

xanthos SSCrazy Points: 2510 More actions · Answer 4

Looks like the bug described in the following KB article:

http://support.microsoft.com/kb/909369/en-us