Help! TempDB just keeps on growing

Question

Help! TempDB just keeps on growing

Sander A.

Default port

Points: 1440
More actions
November 13, 2008 at 4:18 am

#127720

Because of performance reasonse i moved the tempDB to an other disc (which is mucht faster than the old one). After i moved the file the tempDB grew larger and larger to a size of 1 terabyte and when the disc was full some queries dropped because there was no space left for the tempDB.
This problem started after moving the file to an other drive, does anyone know what can cause this problem? Because now we have to shrink the tempDB manually every day to keep the software running.

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

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

free_mascot One Orange Chip Points: 27169 More actions · Answer 1

Try to findout more detail about the processes running on the Tempdb. Find out any open transactions are there or not in Tempdb?

---------------------------------------------------
"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't."

Sander A. Default port Points: 1440 More actions · Answer 2

free_mascot (11/13/2008)
Try to findout more detail about the processes running on the Tempdb. Find out any open transactions are there or not in Tempdb?

But in that case you are asuming this is a general problem, which is not cause by the change we made of the location of the TempDB

free_mascot One Orange Chip Points: 27169 More actions · Answer 3

I doubt just changing location of TempDB is acting like this. There must be something beyond this or we are missiong something.

Have you checked DBCC OPENTRAN ? What is the result?

---------------------------------------------------
"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't."

Sander A. Default port Points: 1440 More actions · Answer 4

free_mascot (11/13/2008)
I doubt just changing location of TempDB is acting like this. There must be something beyond this or we are missiong something.
Have you checked DBCC OPENTRAN ? What is the result?

There are no open transactions but even with shrinkfile i can't shrink the total TempDB size smaller than 30gyg

TheMaskedDBA SSCrazy Points: 2787 More actions · Answer 5

Guys - I have the same problem. This is not a simple A-B-C back to basics problem. This also happend to me after moving tempdb to another drive. I think we're on to something here. I have been a SQL Server Dba for over 12 years and I've seen nothing like it.

We have done the usual due diligence of checking everything. If that doesnt convince you, chew on the fact that when the query errors out with the below error, tempdb shows as 99% free !!!

Could not allocate space for object 'dbo.SORT temporary run storage: 140737593540608' in database 'tempdb' because the 'PRIMARY' filegroup is full.

I will post updates myself, but would appreciate it if anyone who finds out before I do posts it in this thread.

Thanks,

Guru

winash SSChampion Points: 11446 More actions · Answer 6

Sander - what is the initial size of your tempdb datafiles? And what is the autogrowth of these files set to?Maybe the size is set to 30GB and that is why you cannot shrink past that?

Guru - I'm also of the same opinion as free_mascot and don't think that changing the location of tempdb is causing these issues (of course - I could be wrong :).

A google search yielded this on the same error message: http://forums.microsoft.com/TechNet/ShowPost.aspx?PostID=1113034&SiteID=17

Is there some heavy activity happening on the DBs (that perhaps would have coincided with the move)?

Sander A. Default port Points: 1440 More actions · Answer 7

winash (11/13/2008)
Sander - what is the initial size of your tempdb datafiles? And what is the autogrowth of these files set to?Maybe the size is set to 30GB and that is why you cannot shrink past that?

initial size is 16gyg (4 files of 4 gyg), autogrowth is set to 512Mb.

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

When you guys made the "new" TempDB, did you make sure it was still set to the "Simple" recovery mode?

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

Sander A. Default port Points: 1440 More actions · Answer 9

Jeff Moden (11/14/2008)
When you guys made the "new" TempDB, did you make sure it was still set to the "Simple" recovery mode?

moving the TempDB files is as simple as altering the file locations and restarting SQL. So i never touched the recovery mode settings. I did double check it right now and it's still Simple.

GabyYYZ SSCertifiable Points: 7913 More actions · Answer 10

I found this as a possible solution, using shrinkfile, from http://blog.sqlauthority.com/2007/04/01/sql-server-tempdb-is-full-move-tempdb-from-one-drive-to-another-drive/

[font="Courier New"]use tempdb

dbcc shrinkfile(templog, 0)

backup log tempdb with Truncate_Only

dbcc shrinkfile(templog, 0)

[/font]

Avoid the hassle of shrinkdatabase. But maybe have the shrinkfile size larger than zero on the second dbcc shrinkfile.

Gaby________________________________________________________________"In theory, theory and practice are the same. In practice, they are not." - Albert Einstein

Fernando-235287 SSCrazy Points: 2485 More actions · Answer 11

:w00t: Just a crazy question, how often do you reboot the box? maintenance or otherwise? I agree with moving the location won't a lot. I had kind of a similar situation with a datamart server. Due to the high number of transaction and processes run by several datamarts the tempdb was almost impossible to handle. We set a job to run and shrink tempdb, but that worked for a while but at the end it didn't.

Finally we established a reboot routine when the datamarts were not processing anything and tempdb has been behaving normally since (almost 2 years) Just an idea though, every environment is different :w00t:

Sander A. Default port Points: 1440 More actions · Answer 12

Fernando (11/14/2008)
:w00t: Just a crazy question, how often do you reboot the box? maintenance or otherwise? I agree with moving the location won't a lot. I had kind of a similar situation with a datamart server. Due to the high number of transaction and processes run by several datamarts the tempdb was almost impossible to handle. We set a job to run and shrink tempdb, but that worked for a while but at the end it didn't.
Finally we established a reboot routine when the datamarts were not processing anything and tempdb has been behaving normally since (almost 2 years) Just an idea though, every environment is different :w00t:

Normaly we reboot the machine.... never. But because of the new RAID configuration we rebooted it just before the hole problem occured.

But i have to say: the last 2 day's the tempdb is "only" 4x8gyg. So if it stays at this size its fine by me. What the problem was..... i have no idea!

Fernando-235287 SSCrazy Points: 2485 More actions · Answer 13

OK, One of the reboot does with tempdb is, even if you shrinked and it does not reflect the shrinkage, is to regain the space not used. That way we are keeping tempdb under control.

roscoe.louw Newbie Points: 1 More actions · Answer 14

Hi All

I have recently also been experiencing this problem where by the TEMPDb grew to 108GB over a weekend when no one was working and no addition jobs were scheduled other than normal maintenance and I have done no changes to the SQL Environment. I have added maintenance jobs for outside operating hours to keep it small until I come up with a proper solution.

Any advice will be greatly appreciated

Thanks