錯誤訊息
由於文件組 'DEFAULT' 中的磁碟空間不足,無法為資料庫 'TEMPDB' 分配新頁
根因分析
- tempdb 資料檔生長至 4 GB 上限。
- 檔案所在分區為 FAT32,單檔最大 4 GB。
- 程式單一交易關聯 600 萬 + 100 萬筆資料,產生大量中間結果集撐爆 tempdb。
緊急處理(三步驟)
- 限制現有檔案大小:
-- 重啟 SQL Server 讓 tempdb 回歸初始大小,或
DBCC SHRINKFILE (tempdev, 10); -- 縮至 10 MB
DBCC SHRINKFILE (templog, 10);
-- 設定最大 4000 MB (略低於 4 GB)
- 新增多個資料/記錄檔,分散 I/O,各限 4000 MB。
- 修改程式碼(根治):分批提交,避免單一超大交易。
程式優化範例:游標分批處理
-- 方法一:單游標,每筆 Commit(效率較高,無進度追蹤)
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;
-- 方法二:雙重游標 + 進度表(可斷點續傳,效率較低)
CREATE PROCEDURE INSERT_d_2 AS
-- 略,關鍵:外層游標 t_1,內層游標 t_2,每批次更新 progress 表
長期建議
- 將 tempdb 移至 NTFS/ReFS 格式磁碟。
- 配置多個 tempdb 資料檔(建議邏輯 CPU 核心數,上限 8)。
- 改寫大量資料處理為批次/分頁/SSIS/資料流程,避免單一交易。