1

How is tempdb utilized as Failover occurs?

Let's say there are two servers, server 1 is primary and server 2 is secondary. Suddenly, Failover happens, so, how does tempdb behave during this situation?

Improve this question

2 Answers 2

Reset to default
7

Tempdb doesn't move when an AG failover occurs. Each replica is its own standalone instance. Each has its own tempdb. Queries will break during the failover. Once the application reconnects after the failover is complete and crash recovery is also complete, the queries can then run on the other instance where it has its own tempdb.

A Failover Cluster Instance fails over at the instance level, so tempdb is affected. But not for Availability Groups.

Improve this answer
2
  • Most cases yes, SQL 2012 up, you can use tempdb on local disk on each node if you wish.
    – Henrico Bekker
    Commented Sep 11, 2017 at 23:06
  • Ok alright but do we have any good article with respect to this ? I tried to find everywhere but didn't found the relevant one.
    – Vikrant More
    Commented Sep 12, 2017 at 4:04
-1

There is a caveat here... if the local disk with the tempdb fails, SQL will not initiate a failover.

My colleague Twan and I have written an article detailing that on https://www.sqlservercentral.com/articles/do-not-place-tempdb-on-a-local-disk-in-a-sql-failover-cluster

TLDR: you need to check in SQL for the loss of the local disk, and initiate the failover yourself.

Improve this answer
3
  • I think this qualifies more as an interesting comment than an answer - the asker specifically wanted to know TempDB behavior after a failover, but you're talking about a scenario where a failover does not even occur (automatically, at least).
    – LowlyDBA - John M
    Commented Jan 5, 2021 at 21:48
  • I all fairness, its an important piece of the puzzle that blindsided me and my colleague, and it is meant as a supporting/additive statement on Tara's answer. But I now see I could have been more clear about my intention. Therefor I still think it was an appropriate post
    – Theo Ekelmans
    Commented Feb 8, 2021 at 14:18
  • @TheoEkelmans I was confused by your article as MS says SQL Server failover cluster installation supports Local Disk only for installing the tempdb files. As you say, a proper RAID setup with monitoring is important.
    – PeterX
    Commented May 12, 2022 at 1:10

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.