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?

2 Answers 2

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.

2
  • Most cases yes, SQL 2012 up, you can use tempdb on local disk on each node if you wish. 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. 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.

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). 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 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
    May 12 at 1:10

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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