question

ChiragSachdeva-6150 avatar image
0 Votes"
ChiragSachdeva-6150 asked ChiragSachdeva-6150 action

FREE tempdb Without Running FREEPROCCACHE?

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-general
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @ChiragSachdeva-6150, we have not get a reply from you. Did any answers could help you? If there has an answer helped, please do "Accept Answer". If not, please let us know. By doing so, it will benefit for community members who have this similar issue. Your contribution is highly appreciated. Thank you!

0 Votes 0 ·
ErlandSommarskog avatar image
1 Vote"
ErlandSommarskog answered ChiragSachdeva-6150 commented

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
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi,

Thank you for your response.

Below are the answers.

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

  2. What is the typical size of the databases(s) on the instance? single db of 100gb

  3. What is the size of tempdb? Which size does it have when it is "full"? 50gb and 200gb

  4. Do any of the databases have snapshot or read_commmit_snapshot enabled? Any readable secondary? read_commited is the default snapshot isolation level. No readable secondary.

0 Votes 0 ·
ErlandSommarskog avatar image
1 Vote"
ErlandSommarskog answered

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.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

AndreiFomitchev avatar image
1 Vote"
AndreiFomitchev answered

Tempdb size resets after a SQL Server service restart.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

CarrinWu-MSFT avatar image
1 Vote"
CarrinWu-MSFT answered CarrinWu-MSFT edited

Hi @ChiragSachdeva-6150,

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.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

ChiragSachdeva-6150 avatar image
0 Votes"
ChiragSachdeva-6150 answered

Hi @CarrinWu-MSFT @ErlandSommarskog @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.



5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered

I think the answer to your question is simple: stop shrinking tempdb. If your workload requires a tempdb size of, say, 200 GB, let it stay at that size. It's pointless to shrink something that will grow again.

Even more so, when it comes to the log file, size growing a log file always takes resources when SQL Server has to zero out the newly allocated area. The same is true for the data file, if the service account for SQL Server does not have the permission Perform Volume Maintenance Task. (Which it should have under normal circumstances.) And if you some inexplicable reason have to clear the plan cache, you are adding more insult to injury.

So that is the answer to your question: stop shrinking tempdb.

As for why you think that you need to clear the plan cache, my guess is that to shrink the log file, the active portion have to be at the beginning of the file. DBCC FREEPROCCACHE is not going to change that, but as tempdb is being used, time will move it, so you could just as well try DBCC HAVE_SOME_PATIENCE next time. Except, there will not be a next time, because you have now learnt that you not shrink tempdb on regular basis.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.