patternsqlMinor
Should tempdb be larger on a secondary replica?
Viewed 0 times
largertempdbreplicasecondaryshould
Problem
I am working on SQL Server 2012 with Availability Groups.
On the secondary replica, the size of the tempdb datafile is roughly double that of the primary. I am struggling to find information on whether this is normal or not?
The secondary replica is set up as a read-only replica. All the server agent jobs check if they are executing on the primary node when they run, apart from backup jobs which only run on the secondary. As far as I am aware we don't have any client applications that access the secondary replica directly.
Size examples (from
On the secondary replica, the size of the tempdb datafile is roughly double that of the primary. I am struggling to find information on whether this is normal or not?
The secondary replica is set up as a read-only replica. All the server agent jobs check if they are executing on the primary node when they run, apart from backup jobs which only run on the secondary. As far as I am aware we don't have any client applications that access the secondary replica directly.
Size examples (from
sp_helpfile):- Primary replica - tempdev: 12259456 KB
- Secondary replica - tempdev: 24828992 KB
Solution
On secondary node, when used for read-only query, SQL enable ReadCommited Snapshot behind the scene.
If you have long running transaction that does read, that could explain why you used a lot more tempdb on that secondary node.
If you have long running transaction that does read, that could explain why you used a lot more tempdb on that secondary node.
Context
StackExchange Database Administrators Q#265173, answer score: 3
Revisions (0)
No revisions yet.