SQL 重啟時會以sys.master_files記錄的size重建TempDB

2023-01-07

115
0
MSSQL

tempdb.sys.database_files : 記錄目前size
sys.master_files : 記錄configured size / SQL重啟時會以configure size建立Temp DB

-- current size

SELECT name, file_id, type_desc, size * 8 / 1024 [TempdbSizeInMB_dbfile]

FROM tempdb.sys.database_files ORDER BY type_desc DESC, file_id

--configured size

SELECT name, file_id, type_desc, size * 8 / 1024 [TempdbSizeInMB_masterfile]

FROM sys.master_files WHERE DB_NAME(database_id) = 'tempdb' ORDER BY type_desc DESC, file_id

--手動更改configured size

USE MASTER
GO
ALTER DATABASE [tempdb] MODIFY FILE ( NAME = N'tempdev', SIZE = 102400KB)
GO
ALTER DATABASE [tempdb] MODIFY FILE ( NAME = N'temp2', SIZE = 102400KB)
GO

--查看TempDB usage及configured size

--建立Temp table

CREATE TABLE #TempTable (C1 char(3000), C2 char(3000))

SET NOCOUNT ON;

DECLARE @i INT = 1

BEGIN TRAN

WHILE @i <= 110000

BEGIN

INSERT INTO #TempTable VALUES('a','b')

SET @i += 1

END

COMMIT TRAN

DROP TABLE #TempTable

--查看TempDB usage及configured size

--重啟SQL service, 再查看TempDB usage及configured size - TempDB 會以sys.master_files記錄的size重新建立TempDB

若要SQL重啟之後建立特定大小的TempDB，

1. 可先執行下列語法更改configured size後再重啟SQL

USE MASTER
GO
ALTER DATABASE [tempdb] MODIFY FILE ( NAME = N'tempdev', SIZE = 102400KB)
GO
ALTER DATABASE [tempdb] MODIFY FILE ( NAME = N'temp2', SIZE = 102400KB)
GO

或是

2. 成功執行Shrink後，也會更改sys.master_files

Reference
https://www.mssqltips.com/sqlservertip/4829/tempdb-size-resets-after-a-sql-server-service-restart/

回首頁