輔助副本上的 tempdb 是否應該更大？

我正在使用可用性組開發 SQL Server 2012。

在輔助副本上，tempdb 數據文件的大小大約是主副本的兩倍。我正在努力尋找有關這是否正常的資訊？

次要副本設置為只讀副本。所有伺服器代理作業在執行時檢查它們是否在主節點上執行，除了僅在輔助節點上執行的備份作業。據我所知，我們沒有任何直接訪問輔助副本的客戶端應用程序。

尺寸範例（來自sp_helpfile）：

• 主副本 - tempdev：12259456 KB
• 次要副本 - tempdev：24828992 KB

在輔助節點上，當用於只讀查詢時，SQL 在後台啟用 ReadCommited Snapshot。如果您有長時間執行的讀取事務，這可以解釋為什麼您在該輔助節點上使用了更多的 tempdb。

輔助副本上的 tempDB 是否應該更大？

這將取決於您的工作量。

我建議您經常收集並保存您的 tempdb 文件大小。一旦你收集了一段時間來代表你的整個工作負載週期，你就可以繪製它並找到你的基線。這就是我決定所有節點中我的 tempdb 數據庫大小的方式。這是您可以使用的程式碼片段。請先測試。它還將處理“始終線上可用性組”中的數據庫以及您的輔助數據庫。

DECLARE @SQL varchar(MAX);
DECLARE @DBname varchar(MAX);

IF OBJECT_ID('tempdb..#DBs') IS NOT NULL
BEGIN
DROP TABLE #DBs;
END
CREATE TABLE #DBs (dbname sysname);


CREATE TABLE #DB_SizeData(
   [Servername] [nvarchar](50) NOT NULL,
   [DatabaseName] [nvarchar](260) NOT NULL,
   [FileName] [nvarchar](100) not null,
   [Type_Description] [nvarchar] (50),
   [CurrentFileSizeGB] [decimal](18, 1) NULL,
   [FileUsedSpaceGB] [decimal](18, 1) NULL,
   [FileFreeSpaceGB] [decimal](18, 1) NULL,
   [FilePercentFree] [decimal](18, 2) NULL,
   [RecCreateDt] [datetime] NOT NULL
) ON [PRIMARY];

INSERT INTO #DBs
--only choose online databases
SELECT name FROM sys.databases 
--looking for online only 
WHERE [STATE] = 0
--excluding single_user and restricted user
AND   [user_access]=0
AND   [is_read_only]=0
--uncomment below line if you want to exclude system databases
AND   [NAME] NOT IN ('master','model','msdb','tempdb') ; 

--get secondary replica db list
IF OBJECT_ID('tempdb..#AGdatabases') IS NOT NULL
BEGIN
DROP TABLE #AGdatabases;
END

CREATE TABLE #AGdatabases
(dbname SYSNAME);

IF EXISTS (SELECT
AG.name AS [Name],
ISNULL(agstates.primary_replica, '') AS [PrimaryReplicaServerName],
ISNULL(arstates.role, 3) AS [LocalReplicaRole]
FROM master.sys.availability_groups AS AG
LEFT OUTER JOIN master.sys.dm_hadr_availability_group_states as agstates
   ON AG.group_id = agstates.group_id
INNER JOIN master.sys.availability_replicas AS AR
   ON AG.group_id = AR.group_id
INNER JOIN master.sys.dm_hadr_availability_replica_states AS arstates
   ON AR.replica_id = arstates.replica_id AND arstates.is_local = 1
)

BEGIN
INSERT INTO #AGdatabases
SELECT DISTINCT
dbcs.database_name AS [DatabaseName]
FROM master.sys.availability_groups AS AG
LEFT OUTER JOIN master.sys.dm_hadr_availability_group_states as agstates
  ON AG.group_id = agstates.group_id
INNER JOIN master.sys.availability_replicas AS AR
  ON AG.group_id = AR.group_id
INNER JOIN master.sys.dm_hadr_availability_replica_states AS arstates
  ON AR.replica_id = arstates.replica_id AND arstates.is_local = 1
INNER JOIN master.sys.dm_hadr_database_replica_cluster_states AS dbcs
  ON arstates.replica_id = dbcs.replica_id
LEFT OUTER JOIN master.sys.dm_hadr_database_replica_states AS dbrs
  ON dbcs.replica_id = dbrs.replica_id AND dbcs.group_database_id = dbrs.group_database_id
WHERE ISNULL(arstates.role, 3) = 2 AND ISNULL(dbcs.is_database_joined, 0) = 1
ORDER BY  dbcs.database_name;
END
--exclude secondary replica databases
DELETE FROM #DBs WHERE dbname in
(SELECT dbname FROM #AGdatabases);

WHILE exists (SELECT * FROM #DBs)
BEGIN

SELECT TOP 1 @DBname = dbname
FROM #DBs

SELECT @SQL = '
USE '  +  QUOTENAME(@DBname) +' --to account for space or special characters in a datbase name
SELECT @@Servername,
DB_NAME() AS DbName, 
name AS FileName, 
type_desc AS Type_Description,
size/128.0/1024 AS CurrentSizeGB, 
CAST(FILEPROPERTY(name, ''SpaceUsed'') AS INT)/128.0/1024 AS UsedSpaceGB ,
size/128.0/1024 - CAST(FILEPROPERTY(name, ''SpaceUsed'') AS INT)/128.0/1024 AS FreeSpaceGB ,
((size/128.0/1024 - CAST(FILEPROPERTY(name, ''SpaceUsed'') AS INT)/128.0/1024)/(size/128.0/1024))*100 as LogFilePercentFree
,getdate()
from [' + @DBname+ '].sys.database_files 
; 
'
INSERT INTO  #DB_SizeData
EXEC (@SQL)

DELETE FROM  #DBs
WHERE @DBname = dbname
END  

SELECT * FROM #DB_SizeData
DROP TABLE #DB_SizeData
DROP TABLE #DBs

引用自：https://dba.stackexchange.com/questions/265173