It occurred in production environment when I was Unable to Shrink Temp DB Size SQL Server 2008 eventhough nothing was using it.
To check the free space you can use the query below:
SELECT SUM(unallocated_extent_page_count) AS [free pages],
(SUM(unallocated_extent_page_count)*1.0/128) AS [free space in MB]
FROM sys.dm_db_file_space_usage;
and to get idea on files
sp_helpdb 'Tempdb'
When trying to shrink Tempdb it does not let me shrink with command below to 1000MB.
USE [tempdb]
GO
DBCC SHRINKFILE (N'tempdev' , 1000)
GO
I have tried setting intial sizes for tempdb which did not work either,But when we run below query to set the tempdb appropriately with fixed filegrowth then it works
USE [master]
GO
ALTER DATABASE [tempdb] MODIFY FILE ( NAME = N'tempdev', SIZE = 1000MB , FILEGROWTH = 100MB )
--The filegrowth part in the above statement is the real deal which makes the tempdb shrikable.
GO
then run the above shrink command again
USE [tempdb]
GO
DBCC SHRINKFILE (N'tempdev' , 1000)
GO
which works but it may take some time before it releases the space. Trying running shrink command few times giving little time in between the retry’s is good idea if it does not work.
This was really a knickers in a twist but got it resolved at the end which was good because you can afford to restart the instance to free the tempdb space on production.
we can set setting back to autogrow with percentage after this issue is resolved.
Hope this post is helpful. 🙂