Tempdb growing hugely

SQL Server Administration
1

Hi,

We configured the Alwayson on 3 servers. A -primary(Synchronous),B-Secondary-(Async-Readable),C-Secondary(Asyn-Not readable).Fail over mode is Manual.
from 2 days the secondary server tempdb is growing hugely. When we checked the Primary server the tempdb is fine.Not growing.
i am not able to understand why secondary server tempdb is growing hugely. Yesterday night we restarted the Secondary SQL Server ,then also the tempdb didnt became normal. So we increased the space in tempdb drive. By morning the disk got high space alert. Today morning again i restarted the sql server. then tempdb became normal. But still around 55gb is occupied. when i checked the primary server tempdb space . It is also showing around 50 gb.
I am performed shrinking on primary tempdb but no effect.
The all databases are in sync. all together dbs size is around 1.7 TB. The maximum i/o operations are goingon only one db which is 1.2 tb.
As of now i restarted the SQL in secondary so the tempdb became normal.
But i want to know why the tempdb is around 50gb in primary and secondary?
How to trouble shoot tempdb in both servers?
please note that primary server tempdb is not growing. but secondary server tempdb is growing hugely. why is this? now the primary & secondary tempdb size is 50 gb. How to reduce?

(History: 3 days back the primary server completely crashed.. We forced fail over to secondary. then seccondary became primary and all operatins performed on seconadary as it is primary. After A server came up all dbs we restored & Sync in A server and made that again as primary. Now as usual A server is primary and B server is secondary. after this change the tempdb in secondary server was growing hugely)

Please help step wise what kind of trouble shoot i can perform on both primary and secondary tempdbs.

Thanks,
Jo

2

You stated you have 3 servers - with one of the secondaries set at readable. Is that set to read-only or read-intent? Which secondary is having issues with tempdb?

For troubleshooting - you need to understand how tempdb is utilized for the version store and alwayson. If the secondary is readable and someone is running a query - SQL Server keeps a version of the data in the version store. This is to allow the query to complete with a copy of the data as it existed when the query was started.

You also need to review the redo queue and determine if that is backed up.

3

Hi. Thanks for your reply.
Yes versionsstore is keep on growing. Every time i need to restart the sql server.
The secondary server we have it as Read only. Not read intent.
Can you please guide me How to review the redo queue and determine if that is backed up??

4

If the reason tempdb is growing is because of the version store - then restarting SQL Server is resolving the issue. You need more space allocated to tempdb on that system.

You can view the availability group dashboard and add the redo columns to the report. You can also add the send queue as well as other columns to show what is happening across all nodes in the group.

The next thing to review is what is actually being executed on that node. Are there large queries that take hours to run? If so - review them to determine why they are taking so long...these queries will force the version store to keep more data in tempdb.