Huge TempDb file on a Development server

Question

Huge TempDb file on a Development server

MarkThornton

SSCommitted

Points: 1731
More actions
June 2, 2014 at 4:29 am

#308580

On a Development server, we have 5 development versions of a live database, which is properly mounted elsewhere on a production machine.
This morning, I logged in and found that the machine had run out of disk space. The culprit was soon identified: a tempdb file of size 32GB, with a templog file of 1.5GB. I was able to shrink the files in 2 minutes, the log file to 512KB and the tempdb.mdf file to about 28GB. However, going further proved problematic. Eventually, I realised that the Initial Size property of the tempdb.mdf was set to a little above 28GB. I changed it to 8MB and the file shrank to normal size.
As this is a Development machine, I am inclined to link this behaviour to a recent afternoon when I restored the 5 development versions from a copy of our LIVE database. (This is something that we do often, but it is rare that we decide to do it to all 5 at the same time.)
I seem to have restored order on our development server, but I'm slightly outside my comfort zone, so any helpful advice would be appreciated.

Viewing 5 posts - 1 through 4 (of 4 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

There is nothing to do with database restore and Tempdb growth. Tempdb is used for the different purpose; check BOL.

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

Igor Micev SSC-Dedicated Points: 33109 More actions · Answer 2

You should investigate on how the TempDB is being used. You should be able to make a kind of capacity planning for it.

For example if you're doing heavy operations into the tempdb it'll rise up to 32GB again.

Think about relaxing the tempdb by introducing multiple files in it each with same initial size and same autogrowth.

Igor Micev,My blog: www.igormicev.com

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

My suggestion would be to find out what actually made it grow so quickly. If someone did something in coded that's getting ready to go to prod, you could be in deep Kimchee in a couple of days. And, no... database restores don't cause explosive growth of 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)

ScottPletcher SSC Guru Points: 101099 More actions · Answer 4

The restores themselves per se wouldn't cause big tempdb growth.

However, I think it's possible that some settings in those dbs might (for example, snapshot settings).

As noted by all, you do want to review and try to find out what happened. And insure that more disk space can be made available to tempdb if it's required.

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".