TempDB - TempDev will not shrink - Help!

Question

TempDB - TempDev will not shrink - Help!

Viewing 12 posts - 16 through 26 (of 26 total)

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

Kangana Beri SSCrazy Points: 2134 More actions · Answer 1

Thanks David Levy! After trying everything else mentioned in this article and not being successful at shrinking tempdev which is set to initial size of 10 GB but had grown to 80 GB, the DBCC FREEPROCCACHE did the trick.

What is interesting is that tempdev was showing me a used space of only 6 MB before the shrink but still would not shrink the tempdev to its initial size of 10 GB. Any idea why that would happen, I would not expect stuff related to query plans cache to take 70 GB of space anyway!

sebastien piche Ten Centuries Points: 1010 More actions · Answer 2

TempDB is use for all of this action

Query

Triggers

Snapshot isolation and read committed snapshot (RCSI)

MARS

Online index creation

Temporary tables, table variables, and table-valued functions

DBCC CHECK

LOB parameters

Cursors

Service Broker and event notification

XML and LOB variable

Query notifications

Database mail

Index creation

User-defined functions

personaly i create 1 files = to number of cpu and allow 1 gb for each files for intilal size

depend of your environement but if you tempdb grow faster maybe look to allow more memorry to SQL instance

David Levy SSCommitted Points: 1622 More actions · Answer 3

Most of the time you are running into cached temp tables. Query plans with temp tables keep the first page in tempdb when you are done using them so you can quickly run them again DBCC FREEPROCCACHE works well for those. I have heard that there are cases where DBCC FREESYSTEMCACHE('ALL') (don't quote me on exact syntax) is necessary although just waiting and trying again in a few minutes usually does the trick.

Here is a write-up I did on this: http://www.sqlservercentral.com/blogs/adventuresinsql/archive/2009/12/17/how-to-shrink-tempdb-in-sql-2005.aspx. It has links to more resources if you are interested in learning more.

Kangana Beri SSCrazy Points: 2134 More actions · Answer 4

Thanks David, this is good information. We have a system where a lot of temp tables are created and dropped continously by the adhoc reports. If the first page gets physically stored in the tempdb, I can see why it fills up.

But this means that the used space value of 6 MB that I got by running following query is probably not right:

select m.name

, m.size / 128.0 "Initial Size (MB)"

, d.size / 128.0 "Current Size (MB)"

, (fileproperty(m.name, 'spaceused'))/128.0 "Used (MB)"

from sys.master_files m join sys.database_files d

on m.file_id = d.file_id

where database_id = 2

This query was run in tempdb.

Kangana Beri SSCrazy Points: 2134 More actions · Answer 5

Thanks for the list Sebastien.

I have 4 temp db files each with an initial size of 10 GB because we have 4 quad-core CPUs. I did the shrink for all 4 of them individually and the other 3 did shrink, just the tempdev would not shrink.

And I am always debating if I should have 8 files instead of 4 but not sure if that would be benefical.

sebastien piche Ten Centuries Points: 1010 More actions · Answer 6

if I should have 8 files instead of 4 but not sure if that would be benefical form my personal experience the only benifis il reduce contention when you have many querry create et destroy temp object like temp table. for the size my target is 20% of total all active DB size

David Levy SSCommitted Points: 1622 More actions · Answer 7

Before you go the multiple tempdb files route please read this post by Paul Randal: http://www.sqlskills.com/BLOGS/PAUL/post/Search-Engine-QA-12-Should-you-create-multiple-files-for-a-user-DB-on-a-multi-core-box.aspx. He actually wrote the code that does this so you are not going to get a much better expert on it.

sebastien piche Ten Centuries Points: 1010 More actions · Answer 8

http://www.sqlskills.com/BLOGS/PAUL/post/Search-Engine-QA-12-Should-you-create-multiple-files-for-a-user-DB-on-a-multi-core-box.aspx

need to cosider this for my futur system

dbamohsin SSCommitted Points: 1948 More actions · Answer 9

@david-2 Levy - thanks for keeping this topic alive and for your suggestion. I will try this when(!) i come across the issue again.

My DBA Ramblings - SQL Server | Oracle | MySQL | MongoDB | Access[/url]

David Levy SSCommitted Points: 1622 More actions · Answer 10

Oh wow! I did not notice the dates. I just got the notifications yesterday so I thought it was a live thread.

theta SSC Rookie Points: 29 More actions · Answer 11

I've recently had this same issue. What helped me was to open the tempdb database properties in Management Studio, go to the Files tab and decrease the initial Size of the data file. I didn't have to do anything else after that, the database size went down immediately.

Ervin Steckl SSC Enthusiast Points: 100 More actions · Answer 12

"...and not being successful at shrinking tempdev which is set to initial size of 10 GB...

What is interesting is that tempdev was showing me a used space of only 6 MB before the shrink but still would not shrink the tempdev to its initial size of 10 GB. Any idea why that would happen, I would not expect stuff related to query plans cache to take 70 GB of space anyway!

"

In general, during shrink the file size does not go below the minimum (aka initial) size of the file. That's why it won't go below 10GB in your case.