I initially did a search on the forum but nothing really answers my problem.
I have a Tempdb which has grown to around 16gb and my attempts at shrinking it do not work at all! I realise that the problem is resolved with a restart of sql server but this is unacceptable in my production environment (and a restart should never be a norm to fix a problem!)
SELECT name AS 'File Name' , physical_name AS 'Physical Name', size/128 AS 'Total Size in MB',
size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0 AS 'Available Space In MB', * FROM sys.database_files;
O:\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\tempdb.mdf
Total Size in MB: 15891
Available Size in MB: 15783.187500
O:\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\templog.ldf
Total Size in MB: 259
Available Size in MB: 247.187500
select * from sys.dm_db_file_space_usage
The unallocated extent page count when divided by 128 (number of pages in a mb) = 15785.625 mb
Ive tried the following:
DBCC SHRINKFILE (N'tempdev' , 1024)
DBCC SHRINKDATABASE (N'tempdb')
DBCC SHRINKFILE (N'tempdev' , 0, TRUNCATEONLY)
the shrink file gui window of tempdev shows allocated space of 8.00MB and available free space of -96.19mb ????
Can anyone help??
My DBA Ramblings - SQL Server | Oracle | MySQL | MongoDB | Access[/url]