Topic: How to compress tempdb
Now the size tempdb more than 100 , and it is not clear that caused such growth. That tried for compression:
SELECT db_name () [DB], file_id, name, type_desc, physical_name, size/128. [Size (MB)]; CAST (FILEPROPERTY (name, ' SpaceUsed ') AS int)/128.0 [Used Space (MB)]; size/128.0 - CAST (FILEPROPERTY (name, ' SpaceUsed ') AS int)/128.0 AS [Available Space (MB)] FROM sys.database_files;
DB,file_id,name,type_desc,physical_name,Size (MB), Used Space (MB), Available Space (MB)
tempdb, 1, tempdev, ROWS, D:\SQL\MSSQL12.MSSQLSERVER\MSSQL\DATA\tempdb.mdf,114781.500000,9.375000,114772.125000
tempdb, 2, templog, LOG, D:\SQL\MSSQL12.MSSQLSERVER\MSSQL\DATA\templog.ldf,215.000000,6.109375,208.890625
DBCC OPENTRAN; --There are no the active open transactions.
DBCC SHRINKFILE (' tempdev ') --[quote=] DBCC SHRINKFILE: the page 1:14692024 cannot be moved, as it belongs to the work table.
I so understand, the reason in page 1:14692024. How to learn, to what table belongs, who created it. And sacramental "what to do"? Restart of the server was to be considered no yet.