问题描述
我们的tempdb文件不断增长，但我找不到任何错误的事务，数据库是94％未分配的
Our tempdb files keep growing, but I can't find any bad transactions and the database is 94% unallocated
sp_spaceused
sp_spaceused
  database_name database_size
未分配的空间
  tempdb 20639.63 MB
19587.04 MB
database_name database_sizeunallocated space
tempdb 20639.63 MB19587.04 MB
reserved 数据
index_size unused
29272 KB 10800 KB
5616 KB 12856 KB
reserved dataindex_size unused
29272 KB 10800 KB5616 KB 12856 KB
SELECT
[name]
,CONVERT(NUMERIC(10,2),ROUND([size]/128.,2)) AS [Size]
,CONVERT(NUMERIC(10,2),ROUND(FILEPROPERTY([name],'SpaceUsed')/128.,2)) AS [Used]
,CONVERT(NUMERIC(10,2),ROUND(([size]-FILEPROPERTY([name],'SpaceUsed'))/128.,2)) AS [Unused]
FROM [sys].[database_files]
name Size Used Unused
tempdev 512.00 512.00 0.00
templog 1024.00 104.64 919.36
temp2 3615.88 3593.31 22.56
temp3 3616.00 3594.50 21.50
temp4 3615.88 3592.81 23.06
temp5 3615.94 3597.13 18.81
temp6 3615.94 3594.81 21.13
temp7 1024.00 620.44 403.56temp7今天已添加并且已经标记为已使用的620 MB。
temp7 was added today and has 620 MB marked as used already.
这似乎很奇怪即使数据库几乎是空的，e文件也在不断增长。
我在这个论坛和其他人上尝试了很多查询和修复，但没有找到任何解决方案。
It seems very odd that the files keep growing even though the database is nearly empty.
I have tried a number of the queries and fixes on this forum and others, but have not found any solutions.
有什么想法吗？ 谢谢，Dean
Any ideas? Thanks, Dean
推荐答案
根据您的说明， 对于数据文件的文件大小，是固定大小还是无限大小？我不太清楚为什么要向tempdb数据库添加另一个文件。
Per your description, for the file size of the data files, is it fixed size, or unlimited size? I'm not quite clear why you add another file to tempdb database.
此外，您是否尝试过DBCC SHRINKFILE命令来缩小该数据库文件的大小？
Besides, have you tried DBCC SHRINKFILE command to shrink the size of the files for that database?
DBCC SHRINKFILE (Transact-SQL)
最好的问候，
将
这篇关于即使tempdb数据库未分配95％，tempdb文件也在增长的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！