TempDB won't shrink on SQL Server 2014
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
Has anyone come across a problem like this and if so how did you handle it?
Thanks!
Jim
lcohan
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.
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.
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.
https://support.microsoft.com/en-us/topic/how-to-shrink-the-tempdb-database-in-sql-server-ea0a95c2-eff8-7075-9ee2-2ee42226ca1c
https://www.brentozar.com/archive/2016/02/when-shrinking-tempdb-just-wont-shrink/
https://support.microsoft.com/en-us/topic/how-to-shrink-the-tempdb-database-in-sql-server-ea0a95c2-eff8-7075-9ee2-2ee42226ca1c
https://www.brentozar.com/archive/2016/02/when-shrinking-tempdb-just-wont-shrink/
ASKER
We tried all of that and nothing worked except the restart. Just want to make sure I did not overlook something obvious.
Thanks!
Thanks!
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
When there are active transactions, then you cannot resize it.
Imho you should set a meaningful max size and restart the instance.
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.
Imho you should set a meaningful max size and restart the instance.
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.
ASKER
And that is what I told my friend. There must have been an open transaction using TempDB. That is the only thing I can think of.
Thank you everyone!!!!
Thank you everyone!!!!
dbcc opentran;
DBCC INPUTBUFFER;
--and then
sp_who2 spid#