Unable to shrink Tempdb.

Question

DBCC SHRINKFILE: Page 9:2444056 could not be moved because it is a work table page. when trying shrink tempdb facing this issue, we have any solutions for this without restarting SQL services can we shrink thempdb.

Answer 1

Why do you want to shrink tempdb in the first place? Shrinking is a very exceptional event. It is only useful if you know why the item you want to shrink got its current size, you have reason to believe that will not happen again. For instance, someone ran something unbelievable silly and tempdb exploded.

But if you don't know why tempdb has its current size, you should assume it is because the workload requires this size. Shrinking the database will own mean that it will grow again, which takes resources.

Also, there is only point in shrinking if you need the disk space for something else. It is not uncommon to have tempdb on a disk on its own. Why would you shrink in that case?

So tell us, how big is your tempdb and why do feel you need to shrink it? And how big is the biggest user database on the instance?

Answer 2

Hi @Raghavendra Royal ,

Thank you for your reaching out and welcome to Microsoft Q&A!

As proposed by Mike in this thread, work tables are mostly associated with plan cache. Clearing them would remove the work table as well and then you may shrink Tempdb. And this saves you a server restart as well.

There will be some overhead since SQL server will have to recreate execution plans again.

In addition, as Erland says, I'd also be curious about what the actual problem is. Having too many files doesn't hurt you, really.

Here is a thread shares some useful links by Aron, it is worth for us to spend time to check Aron's answer. You need to tune queries if they are heavily using temdbb or may be its the default requirement of your environment. It is acceptable for Tempdb to have 200 G because queries required that much amount of Tempdb space.

Feel free to share your issue here if you have any concerns!

Best regards,

Lucy Chen

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

https://docs.microsoft.com/en-us/answers/support/email-notifications