Somebody did something stupid and tempdb exploded in size. Alerts are firing because the drive is full. I tried shrinking it, and it would not shrink (even though shrink queries said they worked).
There is lots of free space in tempdb (SQL2014 SP1); tempdb > Tasks > Shrink > Files > (shows how much available space I can release)
I shrink (SSMS 2016) to leave more space then the 'minimum shrink to size'. But the files do not actually shrink.
I am working with the business owner, I am watching the server (sp_whoisactive, etc) the server is at rest.
Searching for a solution leads me to When Shrinking Tempdb Just Won’t Shrink(brentozar.com)
I run the following, and it works tempdb, is back to original size.
DBCC FREEPROCCACHE
USE [tempdb]
GO
DBCC SHRINKFILE (N'tempdev' , 2048)
GO
I understand that DBCC FREEPROCCACHE releases all the cache data, and impacts plans. But if it was taking space in tempdb, it should have showed as used space, and not been available for normal shrinking.
Why did this work?
Based on the comments pointing to What is important is that the cached objects are tied to a query plan and that by freeing the procedure cache you can make those objects go away, allowing you to shrink your files. Let me phrase this question from the other way.
Tempdb thinks it has space to shed as evidenced by 'minimum shrink to size', the space that won't go away got created in a temp processes that are no longer running. There is a big chunk of allocated and unused space, why can't I release it without destroying the query plan(s)?
Are the query plans or cache not living on allocated disc space, but still being protected from shrinking?
Analogy: 'minimum shrink to size' shows me the glass is half full/empty but it is not possible to transfer to a smaller glass without breaking the glass and loosing almost all of the contents.
A couple years later, same issue on a SQL 2014 instance, less emergent allows time to try experiment.
If you could somehow identify the query plan, you might be able to delete just that plan releasing the hold on the pages, and then be able to shrink tempdb. I am looking for ways to accomplish this but have not yet been successful.
Maybe you can use DBCC PAGE to see what is on that page so you can address it?
DBCC TRACEON (3604) -- By default, the output is sent to the errorlog. If you want the output to come back to your current connection, turn on trace flag 3604.
GO
DBCC PAGE ([tempdb], 1, 1, 3); -- detailed dump of the PFS page
GO
I see the last page is 937, so I go look at it.
DBCC PAGE ([tempdb], 1, 937, 3);
GO
Nothing clearly an issue there, The last few pages look empty.
I have tried the suggestion by Mike Fields (February 4, 2016 12:01 pm) Multiple attempts did not even free 1MB.
I ran into this problem yesterday, using the SSMS task to shrink files never accomplished anything, then I re-read the documentation on Shrink File and realized I needed to try this:
DBCC SHRINKFILE (N'tempdev' , NOTRUNCATE) -- Move allocated pages from end of file to top of file
DBCC SHRINKFILE (N'tempdev' , 0, TRUNCATEONLY) -- Drop unallocated pages from end of file
I did this for all 8 of the tempdev files and slowly, over time, it finally recovered about 80% of the space. I was truly surprised by this as nothing else had worked. I had to run a job every 15 minutes through the day but it eventually recovered the space
On a SQL 2014 instance I have 'naturally' cleared the cache when a large database was rebuilt with a clustered column store indexes (nightly process). The same SHRINKFILE that would not do anything the previous day works as expected in the morning.
USE [tempdb]
GO
DBCC SHRINKFILE (N'tempdev' , 2000)
GO
Maybe if I could recreate the problem on demand on a 2016+ instance with Query Store, it would be easier to track down exactly what is happening?
Best Answer
This answer is based on comments under the question by @MrTCS pointing to this link and by @KrisGruttemeyer with this summary
As I understand it:
What is happening when you can’t shrink tempdb, as there is a pointer (or something) physically located on the last page(s) of the datafile. It points to the query plan that caused tempdb to fill. That page will not be released as long as that query plan exists. The nuclear option is DBCC FREEPROCCACHE, which clears all the query plans, and the page is released.