select db_name(sa.dbid) as DBname 
	, sa.name as LogicalName
	, case sa.groupid
		WHEN 0 then 'LOG'
		ELSE sfg.groupname
	  end as Filegroup
	, sa.filename as Filename
	, cast(sf.size*8/1024. as numeric(19,3))as sizeMB
	, cast(sf.spaceused*8/1024. as numeric(19,3)) as spaceusedMB
	, cast((sf.size-sf.spaceused)*8/1024. as numeric(19,3)) as freespaceMB
	, case sf.maxsize
		when -1 then 'Unlimited'
		else cast(cast(sf.maxsize*8/1024.  as numeric(19,3))as varchar(22))
	  end as maxsizeMB
	, cast(sf.growth*8/1024. as numeric(19,3)) as nextgrowthMB
from master..sysaltfiles sa 
left join (
	select   cast(size as bigint) as size
			,fileid
			,groupid
			,cast(fileproperty(name,'SpaceUsed')as bigint) as spaceused 
			,cast(maxsize as bigint) as maxsize
			,cast(case 
				when status & 0x100000 = 0 then growth
				else size*growth/100
			 end as bigint) as growth
	from sysfiles
	) sf on sf.fileid=sa.fileid 
		and sf.groupid = sa.groupid
left outer join sysfilegroups sfg on sfg.groupid = sf.groupid
where sa.dbid = db_id()
order by case when sa.groupid = 0 then 1 else 0 end, sa.groupid, sa.fileid

DBCC SHRINKFILE (tempdev6, 220000); 
DBCC SHRINKFILE (tempdev5, 8000); 

SELECT * FROM sys.dm_tran_active_transactions
  WHERE name = N'worktable'

USE [tempdb]
GO
declare @currentSizeMB bigint
		, @newSizeMB bigint
		, @step int = 0
		, @max_steps int = 300
		, @base nvarchar(20) = 'tempdev3'

select @currentSizeMB = sf.size*8/1024
from master..sysaltfiles sa 
left join (
	select   cast(size as bigint) as size
			,fileid
			,groupid
	from sysfiles
	) sf on sf.fileid=sa.fileid 
		and sf.groupid = sa.groupid
left outer join sysfilegroups sfg on sfg.groupid = sf.groupid
where sa.dbid = db_id() and sa.name = @base
 
while @currentSizeMB > 10000  /*feature size*/ AND @max_steps > 0
begin
	SET @max_steps = @max_steps - 1

	DBCC SHRINKFILE (@base , @currentSizeMB)
	
	select @newSizeMB = sf.size*8/1024
	from master..sysaltfiles sa 
	left join (
		select   cast(size as bigint) as size
				,fileid
				,groupid
		from sysfiles
		) sf on sf.fileid=sa.fileid 
			and sf.groupid = sa.groupid
	left outer join sysfilegroups sfg on sfg.groupid = sf.groupid
	where sa.dbid = db_id() and sa.name = @base
	
	if @newSizeMB >= @currentSizeMB and @step = 0
	begin
		SET @currentSizeMB = @newSizeMB -- получалось, что когда сжимаю до текущего размера, то иногда получалось следующим шагом освободить место
		SET @step = 1
	end
	else
	begin
		set @currentSizeMB = @currentSizeMB - 1000
		set @step = 0
	end
end