先分享一段引言

Yeah, lots of free space there … but keep in mind, a data file will grow to it’s size for a reason. Shrinking usually only results in the file growing back. Regardless, if you need to shrink, you need to shrink.

I wouldn’t shrink it all the way down … usually good to leave some room in the tempdb. Actually you should match the amount of data files to the amount of CPU’s for better performance.

治標不治本

(1)壓縮tempdb

use tempdb  
go  
dbcc shrinkfile (tempfile, 10)  
go  
dbcc shrinkfile (templog, 1)  
go

(2)重啟 SQL Server

(3)新增其他磁碟區TempDB檔案

依照微軟的建議的作法，需要依照該台主機的 Logical Processor數的數量來建立TempDB資料檔案，

例如總共有8 Logical Processor則要建立8個TempDB資料檔案，

直接新增即可, 不需要重啟, SQL Server 會自己分配.

Note：加錯路徑怎麼辦@@?

(另外一隻眼睛趕快戳瞎，開玩笑的)

可以的話就趕快移除，不行得話就趕快限制大小。

-- 戳瞎的語法我就不附上來了  
-- 刪除多餘FILE  
DBCC SHRINKFILE ('DataFileRemoval\_2', EMPTYFILE)  
GO


ALTER DATABASE [tempdb]  REMOVE FILE [temp5]  
GO


-- 如果不行,先限制檔案大小  
USE [master]  
GO


ALTER DATABASE [tempdb] MODIFY FILE ( NAME = N'temp5', MAXSIZE = 396500KB )  
GO

額外補充兩點TempDB Configuration建議

• Max Size Setting

建議盡可能將多個file的Max Size設定為一樣,因為SQL Server 會選擇寫入free space較多的file，否則可能照常SQL Server一直寫入同一個檔案,而造成衝突(contention).

• Auto-Grow Setting

盡可能不要使用Auto-Grow，可以測試tempdb尖峰使用空間，以預留足夠的空間。

治本

(1)找出占用tempdb的temp Table是哪張

SELECT o.Name table\_name  
 ,p.used\_page\_count * 8 used\_size  
 ,p.reserved\_page\_count * 8 reserved\_size  
 ,p.row\_count  
FROM sys.dm\_db\_partition\_stats p  
INNER JOIN sys.objects AS o ON o.object\_id = p.object\_id  
WHERE o.type\_desc = 'USER\_TABLE'  
 AND o.is\_ms\_shipped = 0

(2)利用#temp表名稱找出可能的語法

參考

資料庫調校思路分享

(3)找出大量使用TempDB的Script，查看是否有可改善的地方。

這裡就關係到幾個迷思?

(1)Temp Table vs Temp Variable?

(2)Temp Table 沒有 DROP 會有什麼問題?

以及一些觀念

SQL Server狀態 Pending, Runnable, Running, Suspended, Sleeping, Dormant, Background, Spinlock 的區別 (可以參考我部落格的文章)

(1)Temp Table vs Temp Variable?

是否#Temp 與 @Variable 都會存在於Memory中?

我認為最好的理解是, 他們都會存在於TempDB中(On Disk), 但只要記憶體允許, SQL Server就會將其寫入Memory.

他們的差別在於Seesion的可見性

• 對於#temp而言，自己與子session看得到。

• 對於##temp而言，大家都看得到。

• 對於@variable而言，只有自己看得到。

2)Temp Table 沒有 DROP 會有什麼問題?

SQL Server會在session結束後將temp table drop掉，所以理論上沒有drop應該是不會有問題.

不過照我的研究, 還是可能會有問題的，以下這點最有可能，不過尚未測試:

可能會有一些多執行緒的問題, 導致SQL Server 重複創建Temp Table ,而這牽扯到SQL Server 多執行緒是如何共享使用空間, 需經過測試才能確定.

就以上這段合理推測, 就有足夠的理由讓我們在使用Temp Table時, 手動加上DROP語法.

目前遇到的狀況 : 一開始採用治標, 做壓縮, 但tempdb都在使用中, 壓縮後空間也沒釋放

DBCC SHRINKFILE: Page 1:1xxxxx could not be moved because it is a work table page.

可以使用以下語法去釋放掉plan, 以致可以進行壓縮, 但這樣會導致線上plan重新讀取可能會耗效能,並不推薦.

DBCC FREESYSTEMCACHE ('ALL')  
DBCC FREEPROCCACHE

如果想真要使用,因為只想壓縮tempdb,採用以下做法會比較好:

DBCC FREESYSTEMCACHE ('tempdb')  -- clears cache for tempdb  
DBCC FREESYSTEMCACHE ('Temporary Tables & Table Variables') -- clears all the temp table and variables

如果清除執行計畫也不行的話, 必須重啟SQL Server.

原本以為按照以上的方法處理就可以, 結果後續發生了很多問題.

(1)壓縮沒有效果

怎麼知道是什麼原因?

Exec sp\_spaceused

理論上unallocted space應該是你可以壓縮的空間.

reserved+unallocated space + dblog = database_size

或是這個也很好用

DBCC showfilestats
GO
DECLARE @T TABLE
(
FileId   NVARCHAR(200),
FileGroup  NVARCHAR(200),
TotalExtents    NVARCHAR(200),
UsedExtents     NVARCHAR(200),
Name            NVARCHAR(200),
FileName        NVARCHAR(200)
)
INSERT INTO @T 
EXEC('DBCC showfilestats')
SELECT * FROM @T
SELECT Name,FileName,TotalExtents/16/1024 AS GB FROM @T

• Page = 8Kb — — -> Extent = 8 pages :: Extents = 64Kb
• MB = 1024Kb :: MB = 16 Extents

或是(什麼是Version Store?)

SELECT GETDATE() AS runtime,  
    SUM(user\_object\_reserved\_page\_count) * 8 AS usr\_obj\_kb,  
    SUM(internal\_object\_reserved\_page\_count) * 8 AS internal\_obj\_kb,  
    SUM(version\_store\_reserved\_page\_count) * 8 AS version\_store\_kb,  
    SUM(unallocated\_extent\_page\_count) * 8 AS freespace\_kb,  
    SUM(mixed\_extent\_page\_count) * 8 AS mixedextent\_kb  
FROM sys.dm\_db\_file\_space\_usage;

(2)CPU100%

是否因為TempDB是否引起IO latch?

SELECT session\_id, wait\_type, wait\_duration\_ms, resource\_description
FROM sys.dm\_os\_waiting\_tasks
WHERE
(wait\_type like 'pagelatch\_%' or wait\_type like 'pageiolatch\_%') and resource\_description like '2:%'

另一個需要停機的釋放技巧(不過能不停機就不停機吧!)

參考來源:

五餅二魚工作室
很多時候在使用資料庫的時候，都會發現 tempdb 異常長大，那到底是誰使用到那裏呢 ?dotblogs.com.twhttps://support.microsoft.com/zh-cn/help/307487/how-to-shrink-the-tempdb-database-in-sql-server

TEMPDB空间已满_半世笙箫的博客-CSDN博客
数据库tempdb在运行过程中主要会遇到2种报错： 1.tempdb空间已满 2.tempdb日志已满…blog.csdn.netDBCC SHRINKFILE: Page 4:11283400 could not be moved because it is a work table page.
DBCC SHRINKFILE: Page 4:11283400 could not be moved because it is a work table page.social.msdn.microsoft.comDifference between FreeProcCache and FreeSystemCache
What caches does FREESYSTEMCACHE wipe that FREEPROCCACHE doesn't? There are many system caches available in SQL Server…dba.stackexchange.comExplicitly drop temp table or let SQL Server handle it
Thanks for contributing an answer to Stack Overflow! Please be sure to answer the question. Provide details and share…stackoverflow.comDrop or not drop temporary tables in stored procedures
I saw this question quite a many times but I couldn't get the answer that would satisfy me. Basically what people and…stackoverflow.comwhere do the temp table and variable_table to store,the physical disk or memory?
Thanks for contributing an answer to Stack Overflow! Please be sure to answer the question. Provide details and share…stackoverflow.comWhen to Use Temporary Tables vs. Table Variables
February 21, 2017 by It is very beneficial to store data in SQL Server temp tables rather than manipulate or work with…www.sqlshack.comSQL Server中的临时表和表变量有什么区别？
警告 此答案讨论了SQL Server 2000中引入的"经典"表变量。内存OLTP中的SQL Server 2014引入了内存优化表类型。这些变量的表变量实例在许多方面与下面讨论的变量实例不同！（ 更多详细信息 ）。 存储位置…qastack.cn数据库 'tempdb' 的事务日志已满。若要查明无法重用日志中的空间的原因
最常的做法： --1.清空日志 DUMP TRANSACTION tempdb WITH NO_LOG --2.截断事务日志： BACKUP LOG tempdb WITH NO_LOG --3.收缩数据库文件 DBCC…www.cnblogs.com史丹利好熱
這週客戶網站出現了一個效能問題，很快的調查發現是特定SQL查詢慢(10秒)，剛好開始發生的時間點是每週例行的重建索引，本以為是統計值未更新到新的資料分佈，但資料庫有設定 自動更新統計值，客戶重建索引後，也有 執行更新統計值的語法…dotblogs.com.twTempdb Add files require Restart
ADD - no outage required. Although as Sean from Microsoft pointed out, SQL will prefer to use the lower filled files…dba.stackexchange.comSql Server查詢磁碟的可用空間，資料庫資料檔案及日誌檔案的大小及利用率
在MS Sql Server中可以能過以下的方法查詢出磁碟空間的使用情況及各資料庫資料檔案及日誌檔案的大小及使用利用率: select name, convert(float,size) * (8192.0/1024.0)/1024…www.itread01.comDBCC SHRINKFILE: Page 4:11283400 could not be moved because it is a work table page.
DBCC SHRINKFILE: Page 4:11283400 could not be moved because it is a work table page.social.msdn.microsoft.comsp_spaceused (Transact-SQL) - SQL Server
Applies to: SQL Server (all supported versions) Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics…docs.microsoft.comFreeing unallocated space
I have a server that the database drive is running out of space. i have deleted some of the old log data and historical…dba.stackexchange.comShrink a huge file to with emptyfile
I'm using SQL 2012 I have a huge file in my primary file group (1 TB) that used to have BLOB in it. I cleaned up the…social.msdn.microsoft.comHow to remove a SQL Server Data file for a multi-data file database
By: Manvendra Singh | Updated: 2016-08-04 | Comments (2) | Related: More > Database Administration Problem You have a…www.mssqltips.com[SQL Server]TempDB的基本調教 - iT 邦幫忙::一起幫忙解決難題，拯救 IT 人的一天
TempDB可以想像是SQL Server放在硬碟的暫存檔一樣， 除了在記憶體不足時將會使用TempDB來做運算之外， 在其它SQL Server會使用到TempDB的情況如下： 在下SQL Query時使用Group by, Order…ithelp.ithome.com.twhttps://dotblogs.com.tw/jamesfu/2014/05/20/shrinktempdb

When Shrinking Tempdb Just Won't Shrink - Brent Ozar Unlimited®
I am not a proponent of shrinking databases, but sometimes you have to because your momma said to. Sometimes that…www.brentozar.comBest Practices for SQL Server Tempdb--Multiple Files
This part of a three-part article consolidating a number of best practices for configuring SQL Server tempdb focuses on…logicalread.comBest Practices for SQL Server Tempdb--Initial Sizing
Learn best practices for configuring SQL Server tempdb, including where to place it, initial sizing and configuring…logicalread.comBest Practices for SQL Server Tempdb--Placement
Learn best practices for where to place SQL Server tempdb.logicalread.comBest Practices for SQL Server Tempdb--Multiple Files
This part of a three-part article consolidating a number of best practices for configuring SQL Server tempdb focuses on…logicalread.comwhy i can't shrink tempdb data file - SQLServerCentral
why i can't shrink tempdb data file - Learn more on the SQLServerCentral forumswww.sqlservercentral.comhttps://blog.sqlauthority.com/2017/03/21/sql-server-dbcc-shrinkfile-page-126423878-not-moved-work-table-page-tempdb-files/

https://www.mssqltips.com/sqlservertip/1629/determine-free-space-consumed-space-and-total-space-allocated-for-sql-server-databases/

• Tags:
• SQL Server
• Tempdb

• ← Session的八種狀態-Pending, Runnable, Running, Suspended, Sleeping, Dormant, Background, Spinlock - SQL Server
• SQL Server Management Studio(SSMS)占滿C磁碟區處理 - SQL Server Management Studio →