Post reply

AG secondary TEMPDB growing hugely

  • July 17, 2019 at 5:13 am

    #3663660

    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

  • July 17, 2019 at 7:20 am

    #3663674

    It might be something to do with how secondary replicas use RCSI. See what you think:

    https://www.red-gate.com/simple-talk/sql/performance/read-committed-snapshot-isolation-high-version_ghost_record_count/

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply