FREE tempdb Without Running FREEPROCCACHE?

Chirag Sachdeva 281 Reputation points
2021-04-17T12:22:55.397+00:00

Hi Folk,

Whenever tempdb gets full, without running dbcc freeproccache it does not shrink. Is there any way we can avoid freeproccache to release tempdb space?

Thanks
Chirag

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,642 questions

6 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Erland Sommarskog 100.8K Reputation points MVP
    2021-04-17T13:21:46.627+00:00

    DBCC FREEPROCCACHE as such does not free up space in tempdb. If you see this, it could be that you have query plans with spool operators or hash/sort operators that spills to disk, so that when these queries are executed, a lot of tempdb space is consumed. When you clear the plan cache, the queries are sniffed for different parameters, and now execute with less or no disk spill.

    But this quite a bit speculative. You have offered very little information. As a start, answer these questions:

    1. What is the output from "SELECT @@version".
    2. What is the typical size of the databases(s) on the instance?
    3. What is the size of tempdb? Which size does it have when it is "full"?
    4. Do any of the databases have snapshot or read_commmit_snapshot enabled? Any readable secondary?
    1 person found this answer helpful.
  2. Erland Sommarskog 100.8K Reputation points MVP
    2021-04-18T12:54:04.5+00:00

    What is the output from "SELECT @@version". SQL Server 2017 (RTM)

    Please post the full output. It may be relevant.

    Beside tempdb being full, do see any other issues? Are users complaining about poor performance?

    When tempdb gets "full", you can look in the DMV sys.dm_db_task_space_usage, to see which sessions that are taking up space. You can then see what their current activity is. Maybe that can give a hint about resource-consuming queries. (If that is the issue.)

    Also, if you have not already done so, you should enable Query Store for your database. (Not tempdb, but the one that is 100 GB.) When tempdb gets "full", you can check what has been the most resource-consuming queries the last hour, and then run sp_recompile one of tables in the top query, to see if that helps.

    The reason I keep putting "full" in quotes is that one resolution problem is simply to get more disk. That is certainly what I would look into, if there are no other issues.

    1 person found this answer helpful.
    0 comments
  3. AndreiFomitchev 91 Reputation points
    2021-04-18T22:06:00.717+00:00

    Tempdb size resets after a SQL Server service restart.

    1 person found this answer helpful.
    0 comments
  4. CarrinWu-MSFT 6,851 Reputation points
    2021-04-19T06:36:26.757+00:00

    Hi @Chirag Sachdeva ,

    Welcome to Microsoft Q&A!

    If you execute DBCC FREEPROCCACHE, it will removes all elements from the plan cache, removes a specific plan from the plan cache by specifying a plan handle or SQL handle, or removes all cache entries associated with a specified resource pool. Please refer to DBCC FREEPROCCACHE to get more information.
    When you facing a problem about TempDB is full, please find out who is using the TempDB first and then try to fix it. FREEPROCCACHE will clear the procedure cache and will cache the data newly, and then shrink database, this solution will temporary solve this issue. The permanent fix is hard one, please get the query or tasks from this blog, it will help you fix this issue.

    Best regards,
    Carrin

    If the answer is helpful, please click "Accept Answer" and upvote it.
    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.

    1 person found this answer helpful.
    0 comments
  5. Chirag Sachdeva 281 Reputation points
    2021-05-02T07:13:52.143+00:00

    Hi @CarrinWu-MSFT @Erland Sommarskog @AndreiFomitchev Thanks for all your inputs.

    I know what DBCC freeproccache does and also know how to investigate tempdb db usage. The one thing that is bothering me is that when tempdb gets full (internally free 99.9%) and when I shrink it, it sometimes gets shrunk fine but sometimes I have to run freeproccache to shrink it because if i run shrink command without running freeproccache, shrink command completes fine but tempdb does not shrinks. I hope I am able to convey my query well.

    0 comments