SQL Server tempdb 磁盤空間不足？FAT32 4GB 限制與大數據量優化實戰

SQL Server tempdb 磁盤空間不足？FAT32 4GB 限制與大數據量優化實戰

錯誤：「由於文件組 ‘DEFAULT’ 中的磁盤空間不足，無法為數據庫 ‘TEMPDB’ 分配新頁」。表面是 tempdb 滿了，實際是資料檔案所在分區為 FAT32，單檔案上限 4GB，導致自動增長失敗。

緊急處理三步走

1. 縮回預設大小並設上限：

   DBCC SHRINKFILE (tempdev, 10);
   DBCC SHRINKFILE (templog, 10);
   -- 然後在屬性中將「最大大小」設為 4000 MB

2. 新增多個資料/日誌檔案：分散到不同磁碟，各設 4000 MB 上限，繞過單檔 4GB 限制。
3. 根治：優化大數據量程式：案例中 600 萬表關聯 100 萬表一次性寫入，導致 tempdb 暴增。必須改為 分批提交、避免長事務、使用游標/迴圈分頁處理。

分批處理範例（兩種模式）

模式一：單游標，高效但無進度追蹤

CREATE PROCEDURE INSERT_d_1 AS
DECLARE @name1 VARCHAR(4000), @name2 VARCHAR(4000), @id INT;
DECLARE c1 CURSOR FOR
  SELECT t1.name, t2.name, t1.id FROM t_1 t1 JOIN t_2 t2 ON t1.id=t2.id ORDER BY t1.id;
OPEN c1;
FETCH NEXT FROM c1 INTO @name1,@name2,@id;
WHILE @@FETCH_STATUS=0 BEGIN
  INSERT INTO test_1 VALUES(@name1,@name2,@id);
  FETCH NEXT FROM c1 INTO @name1,@name2,@id;
END
CLOSE c1; DEALLOCATE c1;

模式二：雙層游標，可追蹤進度（寫入追蹤表 t_s），效率較低

CREATE PROCEDURE INSERT_d_2 AS
DECLARE c1 CURSOR FOR SELECT name, id FROM t_1 ORDER BY id;
OPEN c1;
FETCH NEXT FROM c1 INTO @name1,@id;
WHILE @@FETCH_STATUS=0 BEGIN
  DECLARE c2 CURSOR FOR SELECT name FROM t_2 WHERE id=@id;
  OPEN c2;
  FETCH NEXT FROM c2 INTO @name2;
  WHILE @@FETCH_STATUS=0 BEGIN
    INSERT INTO test_1 VALUES(@name1,@name2,@id);
    UPDATE t_s SET id=@id, date=GETDATE();  -- 記錄進度
    FETCH NEXT FROM c2 INTO @name2;
  END
  CLOSE c2; DEALLOCATE c2;
  FETCH NEXT FROM c1 INTO @name1,@id;
END
CLOSE c1; DEALLOCATE c1;

核心教訓

• 硬體/檔案系統限制（FAT32 4GB）不可忽視，生產環境務必用 NTFS/ReFS。
• tempdb 擴容治標不治本，大批量操作必須分批、頻繁提交，避免長事務撐爆 tempdb。
• 追蹤表 + 可恢復設計，讓異常中斷後能續跑，是批量作業的工程化基本素養。