1

Topic: How to compress tempdb

I welcome.
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.

2

Re: How to compress tempdb

virtuOS;
The server version what? With 2012 on mine appeared sys.dm_db_page_allocations

3

Re: How to compress tempdb

Microsoft SQL Server 2014 (SP2-CU8) (KB4037356) - 12.0.5557.0 (X64)

4

Re: How to compress tempdb

virtuOS wrote:

DBCC OPENTRAN;
--There are no the active open transactions.

also what?
- I was connected to basis and created the temporary table, I sit and I meditate on it
- Or I do of it what or
- Or generally launched  to basis in which the server constructed such plan where  that  on a disk in

5

Re: How to compress tempdb

Use search. Was recently . Topics with councils and recipes.
1 ? Happens.... smile

6

Re: How to compress tempdb

LSV wrote:

Use search. Was recently . Topics with councils and recipes.
1 ? Happens.... smile

It not 1.
Search used, all came to an end with server restart that it would not be desirable yet.

7

Re: How to compress tempdb

virtuOS;
Somewhere is  , it is possible with MISSING JOIN PREDICATE. Or from area "to delete everything, to calculate everything, collecting everything, to fill all".

8

Re: How to compress tempdb

virtuOS;
Disconnect all connections to the server and compress. Only it then will again be unclenched.

wrote:

belongs to the work table

Any process fulfills request in which plan there is a reversal to tempdb in any basis.

9

Re: How to compress tempdb

dbcc shrinkfile (' tempdev ', xxx)
Where  - the finite size in a MB.
At first install value hardly less occupied with a file, then reduce by 20-30 %.
But the file will be compressed, if there are no the active processes in tempdb.