<div>
As far as I'm aware you will need to restart that SQL Server Service as tempdb gets recreated on every SQL restart and as far as I'm aware that's the best way to do it.
</div>


<div>
I have created a maintenance plan to shrink the database, also sql code after to change the log database mode to Simple Recovery, shrink and then set it back to full recovery. Every evening the maintenance plan runs and I have had no issues so far.
</div>


<div>
Well if nothing works as already tried above the SQL restart would do that but maybe try the DBCC FREEPROCCACHE first and see if that helps but if that doesnt...maybe only restart would help.<br><a href="https://support.microsoft.com/en-us/topic/how-to-shrink-the-tempdb-database-in-sql-server-ea0a95c2-eff8-7075-9ee2-2ee42226ca1c" rel="ugc">https://support.microsoft.com/en-us/topic/how-to-shrink-the-tempdb-database-in-sql-server-ea0a95c2-eff8-7075-9ee2-2ee42226ca1c</a><br><a href="https://www.brentozar.com/archive/2016/02/when-shrinking-tempdb-just-wont-shrink/" rel="ugc">https://www.brentozar.com/archive/2016/02/when-shrinking-tempdb-just-wont-shrink/</a><br><br>
</div>


<div>
We tried all of that and nothing worked except the restart. &nbsp;Just want to make sure I did not overlook something obvious.<br><br>Thanks!&nbsp;&nbsp;
</div>


Sr Database Administrator

Jim Youmans

<div>
When there are active transactions, then you cannot resize it.<br />
<br />
Imho you should set a meaningful max size and restart the instance.<br />
<br />
But keep in mind, this size can be an indicator for a huge workload. So you should examine whether this is from a single event or whether your workloads have increased over time. In the latter case you need more disk space.
</div>


arnold

<div>
And that is what I told my friend. &nbsp;There must have been an open transaction using TempDB. &nbsp;That is the only thing I can think of.<br><br>Thank you everyone!!!!
</div>


<div>
<pre><code class="code-1 nolinks" id="code-20-43285047-1"><span>dbcc opentran; </span>
<span>DBCC INPUTBUFFER;</span>
<span>--and then </span>
<span>sp_who2 spid#</span>
</code></pre>should help you identify the offender and eventually kill that SPID only assuming that can be safely done without causing any data issues.
</div>


<div>
<div class="content wysiwyg-content fr-view">
I was helping a friend with a TempDB problem. &nbsp;He is running SQL Server 2014 on Azure VM and it is part of an always on group. &nbsp;The problem is that TempDB grew to fill up the drive and when we tried to shrink it, nothing worked. &nbsp;We did the DBCC FREESYSTEMCACHE('ALL') &nbsp;and shrink. We did alter TempDB size. &nbsp;We did everything we could think of and nothing worked. &nbsp;We finally failed the database over to secondary server and stopped and restarted the SQL instance to reset TempDB on the primary server. &nbsp;That worked.<br><br>Has anyone come across a problem like this and if so how did you handle it?<br><br>Thanks!<br>Jim
</div>
</div>



I was helping a friend with a TempDB problem.  He is running SQL Server 2014 on Azure VM and it is part of an always on group.  The problem is that TempDB grew to fill up the drive and when we tried to shrink it, nothing worked.  We did the DBCC FREESYSTEMCACHE('ALL')  and shrink. We did alter TempDB size.  We did everything we could think of and nothing worked.  We finally failed the database over to secondary server and stopped and restarted the SQL instance to reset TempDB on the primary server.  That worked.
Has anyone come across a problem like this and if so how did you handle it?
Thanks!
Jim

TempDB won't shrink on SQL Server 2014

Microsoft SQL Server is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.SQL Server is available in multiple versions, typically identified by release year, and versions are subdivided into editions to distinguish between product functionality. Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning.

Microsoft SQL Server

Microsoft Azure is a cloud computing platform and infrastructure for building, deploying and managing applications and services through datacenters. It provides both platform-as-a-service (PaaS) and infrastructure-as-a-service (IaaS) services and supports many different programming languages, tools and frameworks, including both Microsoft-specific and third-party software and systems. Cloud Services is a PaaS environment and can be used to create scalable applications and services; there are specific software development kits (SDKs) provided by Microsoft for Python, Java, Node.js and .NET. Azure also has file and storage services, data management, analytics and DNS services.