Ellie's blog

-- 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/