DBCC FREEPROCCACHE

Question

Post reply

DBCC FREEPROCCACHE

SQLAssAS

SSCertifiable

Points: 7246
More actions
September 11, 2013 at 2:45 am

#395456

What are the best practices around using DBCC FREEPROCCACHE to free up space in TEMPDB when it won't shrink?
TEMPDB ran out of space on one of our systems, and therefore the query was cut short. It now won't shrink. sys.dm_exec_requests is showing that nothing is happening on tempdb, there are no locks either.
It is a shared, production system so we do not want to restart the instance.

Viewing 7 posts - 1 through 6 (of 6 total)

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

Dennis Post SSCarpal Tunnel Points: 4473 More actions · Answer 1

Are you able to increase the size of TempDB or add extra data files to it?

Correct me if I'm wrong people but, the Proccache is stored in memory and not the TempDB.

For better, quicker answers on T-SQL questions, read Jeff Moden's suggestions.[/url]

"Million-to-one chances crop up nine times out of ten." ― Terry Pratchett, Mort

Dennis Post SSCarpal Tunnel Points: 4473 More actions · Answer 2

This article by Kimberly Tripp[/url] maybe useful to you.

I'm reading it and the related articles now.

For better, quicker answers on T-SQL questions, read Jeff Moden's suggestions.[/url]

"Million-to-one chances crop up nine times out of ten." ― Terry Pratchett, Mort

SQLAssAS SSCertifiable Points: 7246 More actions · Answer 3

Dennis Post (9/11/2013)
Are you able to increase the size of TempDB or add extra data files to it?
Correct me if I'm wrong people but, the Proccache is stored in memory and not the TempDB.

Dennis,

From what I have read, when a transaction is cut short due to TEMPDB Running out of space, the items stored in tempdb get stuck, and can not be shrunk out. When the same process is ran again, for example an SP which created all the temp files, it will then pick up these temp files again and not re-create them.

I am not sure what the process was other wise i would run it again. Adding more files won't help in this situation and I have already free'd up more space on the drive to allow it to function in the interim.

SQLAssAS SSCertifiable Points: 7246 More actions · Answer 4

Dennis Post (9/11/2013)
This article by Kimberly Tripp[/url] maybe useful to you.
I'm reading it and the related articles now.

Thanks will read up

I got the idea from here by the way

http://www.sqldbadiaries.com/2010/11/13/tempdb-please-allow-me-to-shrink-you/

Grant Fritchey SSC Guru Points: 398694 More actions · Answer 5

If you did hit a situation where this might help, I'd be sure to use FREEPROCCACHE with the parameter for the specific plans you want to remove rather than blasting all plans out of the cache. That's a much safer and sane approach.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

MyDoggieJessie SSC-Forever Points: 44486 More actions · Answer 6

You could also check to see if there are any lingering/old/unused spids that can be killed - in our shop we have report writers that will run rather large queries using temp tables or huge aggregates, then leave for the day keeping their management studio open...from what I've seen in the past, the "work" these spids were doing remain in tempdb until their session is destroyed. Killing these types of spids would remove their portion of the space from the tempdb

______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience