Part of the DBA-Tools Project.
When Did This FCI Last Move, and Why
On a Failover Cluster Instance (FCI), every node blip, planned or not, causes SQL Server to restart on whichever node picks it up. That restart resets sqlserver_start_time, which makes instance uptime itself a clue: a suspiciously short uptime on an FCI you didn’t knowingly fail over is worth investigating, not ignoring.
This script pulls any error log entry mentioning “failover” alongside the current instance start time, so you can see both in one place: whether a failover happened recently, and exactly how long ago the instance came online on this node.
Why Last Node Blip Matters
- A short instance uptime on a standalone server usually just means a patch or restart, the same short uptime on an FCI can mean an unplanned failover nobody’s investigated yet
- The default trace and error log archive only hold a limited window, an old failover can roll off before anyone thinks to check
- Repeated blips (several restarts close together) point at a specific unstable node or a flapping resource, not a one-off event
- If nothing comes back, that’s useful too: no failover-related entries in the current error log archive, confirmed against a known instance uptime
When to Run This Script
- Any time an FCI shows an unexpectedly recent
sqlserver_start_time - Investigating a reported “blip” or brief outage on a clustered instance
- Routine health checks on any FCI, to establish a clean baseline for comparison later
- Before escalating a suspected node issue to the Windows clustering team, to bring a concrete timestamp rather than a vague “it seemed to restart”
The Script
/*
Script Name : Get-LastNodeBlip
Category : high-availability
Purpose : Returns SQL Server error log entries that mention failover, alongside the current
instance start time. On an FCI, every node blip causes SQL Server to restart on the
receiving node — sqlserver_start_time is when it last came online.
If no rows are returned there are no 'failover' entries in the current error log
archive; use the PowerShell Get-WinEvent approach to query the Windows Failover
Clustering Operational log directly (see blog post).
Author : Peter Whyte (https://sqldba.blog/dba-scripts-get-last-node-blip/)
Requires : VIEW SERVER STATE
*/
-- Blog: https://sqldba.blog/dba-scripts-get-last-node-blip/
-- SAFE:ReadOnly
-- IMPACT:Low
SET NOCOUNT ON;
CREATE TABLE #ErrorLog (
LogDate DATETIME,
ProcessInfo NVARCHAR(100),
[Text] NVARCHAR(MAX)
);
INSERT INTO #ErrorLog
EXEC xp_readerrorlog 0, 1, N'failover';
SELECT
e.LogDate AS event_time,
CAST(SERVERPROPERTY('ServerName') AS NVARCHAR(256)) AS server_name,
CAST(SERVERPROPERTY('IsClustered') AS BIT) AS is_clustered,
osi.sqlserver_start_time AS instance_last_started,
DATEDIFF(DAY, osi.sqlserver_start_time, GETDATE()) AS days_up,
e.ProcessInfo AS source,
e.[Text] AS message
FROM #ErrorLog e
CROSS JOIN sys.dm_os_sys_info osi
ORDER BY e.LogDate DESC;
DROP TABLE #ErrorLog;
Because this is a CROSS JOIN, no rows come back at all if no error log entries mention “failover”, not even a status row with the instance uptime. If you need the uptime regardless of findings, sys.dm_os_sys_info alone answers that.
How To Run From The Repo
Clone DBA Tools, initialize and run the script:
# Clone dba-tools repo:
git clone https://github.com/peterwhyte-lgtm/dba-tools
# Initialize environment:
cd dba-tools
.\Initialize-Environment.ps1
# Check the error log for failover mentions, alongside instance uptime:
.\run.ps1 Get-LastNodeBlip
# To run against a remote sql server:
.\run.ps1 Get-LastNodeBlip -ServerInstance SQLSERVER01
This script lives in the repo at:
Example Output
Real output from this lab instance, not staged: no rows. is_clustered = False on this instance (a standalone box, not an FCI), and separately confirmed sqlserver_start_time shows an uptime of 4 days, with nothing failover-related in the current error log archive. That’s the honest, correct combination for a standalone instance that has never failed over. On an FCI with a genuine recent blip, a row looks like this:
| event_time | is_clustered | instance_last_started | days_up | message |
|---|---|---|---|---|
| 2026-07-20 03:14:02 | 1 | 2026-07-20 03:15:47 | 0 | The SQL Server Network Interface library could not register the Service Principal Name… failover partner… |
Understanding the Results
- No rows at all — no “failover” mentions in the current error log archive; combine with a separate uptime check to confirm whether that’s genuinely no recent blip or just outside the retained log window
- is_clustered = 0 (False) — this instance isn’t an FCI at all, a restart here is a normal standalone restart, not a node blip
- days_up much lower than expected on a known FCI — worth investigating even without a matching error log entry, since the retained archive can roll off
- Repeated recent event_time entries — points at a specific unstable node or a flapping cluster resource, worth escalating to the Windows clustering team with these exact timestamps
Best Practices
- Establish a baseline uptime check on every FCI as part of routine health checks, so a sudden drop is obvious even without a matching error log entry
- If nothing comes back but uptime still looks suspiciously short, query the Windows Failover Clustering Operational log directly via
Get-WinEvent, the SQL error log archive has a limited retention window the Windows cluster log doesn’t share - Bring the exact
event_timeandinstance_last_startedvalues when escalating to a clustering or infrastructure team, precise timestamps correlate directly against Windows cluster and network logs - Don’t dismiss a short uptime on an FCI as “just a patch” without checking, patches are planned and expected, an unplanned blip is not
Related Scripts
You may also find these scripts useful:
- High Availability (hub)
- AG Replica Role and Synchronization State
- Mirroring Endpoint Health and Status
Frequently Asked Questions
What counts as a “node blip” on an FCI?
Any event that causes the SQL Server service to move between cluster nodes, whether a manual failover, a resource health check failure, or a node-level issue like a network blip or a hardware fault. Each one restarts the SQL Server instance on whichever node picks it up.
Why does this only check the error log instead of the Windows cluster log directly?
The SQL Server error log is queryable from T-SQL alone, no separate Windows access needed, which makes it the fastest first check. The Windows Failover Clustering Operational log has a longer, more complete history and is worth checking via PowerShell’s Get-WinEvent when the SQL error log archive has already rolled off the event you’re chasing.
Summary
An FCI that restarted recently either did so on purpose or didn’t, and the difference matters. This script gives you both halves of that answer in one query: whether the error log shows anything failover-related, and exactly how long the instance has actually been up on this node.
Run it as a routine check on every FCI, and treat a short uptime with no obvious planned cause as worth chasing down, even if this script alone comes back empty.
Leave a Reply