2

We had a strange issue post our regular OS patching for SQL servers.

Per best practices, we apply patch on Passive and do node failovers to make current passive, active and vice versa to complete patching.

Generally node failover is seamless and completes under a min. However recently we had issue where it took 4 minutes to bring SQL online after the node failover:

I am checking logs and events but could not find the reason: below is the findings so far:

Note: The SQL server is running on VM

  1. No increased in SQL server activity
  2. Databases are part of DB mirroring
  3. No increase in user connections or user queries running longer in that duration
  4. VLF under 500 for all databases
  5. NO CPU/Memory pressure and LPIM is enabled.
  6. The process which was seemed to killed time out long running during failover was EXEC sp_server_diagnostics 20 running for past 86745234 secs

Please assist what else should i be checking to find the root cause?

Edit- I tried analyzing the cluster log and can see sql offline was initiated but i am not sure where it spend atleast 4 mins internally to actually shut down sql and bring it back. after 4 mins sql error log had all enteries for databases being brought up approx 10 secs. So it looks DB might not hve any involvement here for slowing down process.

Edit- some VLF info when checked currently

enter image description here

6
  • 1
    Both many VLFS, and large VLFs, have caused slow failovers in my experience. Just because there are < 5000 doesn't mean they're small. Might be something else to look into. – Erik Darling Jan 29 at 19:01
  • @ErikDarling: I agree. I am cheeking the sizes of those VLFs as well. I am checking my best for that something else , just seem to be running out of ideas if something out of the blues just happened here – BeginnerDBA Jan 29 at 19:06
  • Is this an FCI or an AG? It sounds like you're describing an FCI, but it's not explicit. :) – AMtwo Jan 29 at 20:49
  • Also--how much memory do you have on these servers? – AMtwo Jan 29 at 20:51
  • @AMtwo: This is 2 node FCI and memory is 128 GB with 16 logical processors – BeginnerDBA Jan 29 at 21:08
5

Most likely your problem is caused by the databases going through recovery to redo or undo transactions that haven't been hardened to data files.

Avoid recovery all together

Before doing planned server reboots or failovers of an FCI, particularly one with a lot of memory, I like to run a CHECKPOINT on every database. This minimizes the time spend on a clean shutdown of all databases, and (when databases aren't shut down cleanly) minimizes crash recovery time on restart.

I use sp_ineachdb from the First Responder Kit to do this:

EXEC DBA.dbo.sp_ineachdb 'CHECKPOINT;`;

If you hate free code that makes your life easier, you could do something with dynamic SQL:

DECLARE @sql nvarchar(max) = N'';

SELECT @sql += N'CHECKPOINT ' + QUOTENAME(name) + '; '
FROM sys.databases;

EXEC sys.sp_executesql @stmt = @sql;

But make recovery faster by doing less work

And of course, as Erik, Darling mentioned in the comments, make sure your VLFs are in order and properly sized. Scanning those VLFs during crash recovery is what can cause you all the pain you're seeing. For planned maintenance you can CHECKPOINT to minimize or eliminate crash recovery. But if you have... uhhh.... a crash and failover unexpectedly, that crash recovery is still going to happen, and there's not much you can do about it.

Be less direct

I've also had tons of luck with indirect checkpoints. We've rolled this out across our entire environment to much success.

And lobby the powers that be for an upgrade

SQL Server 2019 includes a feature called Accelerated Database Recovery, which can speed up recovery process, particularly when there are long-running, large transactions. ADR is not just for recovery after a crash, but also helps in other scenarios where the transaction log needs to be recovered–including Availability Group secondary redo and Failover Cluster Instance failovers.

5
  • Andy- Thanks for great explanation. I totally agree and will check on those points. Its just this has happened for first time with this app. Its always under a min so wandering if something strange happened. Few errors from cluster log that i see are RSH call deadlock monitor timing out and other message which says failed to stop service with error 1460 – BeginnerDBA Jan 29 at 22:09
  • The "failed to stop service" errors indicate that shutdown took a long time (possibly due to checkpoints, but it could be something else). It's very possible that the instance shutdown didn't complete cleanly, or it just took a very long time. In fact, my answer is in the context of startup being slow, but it sounds like it could be shutdown was slow. The above feedback still stands, though! – AMtwo Jan 30 at 13:34
  • @Amtwo- Just had a quick question , when above of your solutions we were discussing. Instead of running checkpoint ( which many believed will need a downtime) when running before node failover and we dont know how much time it will take for TB size of databases, could running log backup job on the instance will do? i read books online that log backup also kick off checkpoint – BeginnerDBA Feb 5 at 18:20
  • Checkpoints do not require downtime. SQL Server will do checkpoints lazily in the background normally. Explicitly running a checkpoint is no different and requires no downtime. – AMtwo Feb 6 at 0:48
  • @AMtwo- Thank you. Also do you think log backup can run as an alternative to checkpoint. Its ok if you want me to post that as separate question – BeginnerDBA Feb 8 at 17:31

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.