Recently, our blocked processes dashboard has been reporting blocked processes around the time when we do our statistic update.
The reason was quickly found: an update statistics job step (T-SQL) that is starting on both the secondary and the primary SQL Server instance. The job updates several statistics on the same database, that is part of an AlwaysOn Availability Group. I would expect this to fail on the secondary instance.
A quick rundown of the failover history:
Server A, which should stay active due to licensing (will be named Active Server), failed over unexpectedly to Server B (Passive Server) on 20/02 at 9PM.
After the unplanned failover, we did another (but this time planned) manual failover back to the Active Server on 21/02 at 12PM.
Before the first failover all was good, and the active server is the only one running the job.
During the short time that the passive server was the primary replica, we don't have any monitoring and the job history was cleared.
After the failover, back to the 'normal' state, after being on primary on the passive node for less than 24 hours, the job step on the passive instance has also been starting and running on the active instance.
Now the interesting part to me, is that both jobs are running on the active server, seeming like the job is using the listener to get to the database. But it could be an entirely different reason.
There is a copy job PowerShell task running nightly at 01 AM (dbatools):
powershell.exe Copy-DbaAgentJob -ExcludeJob "CopyJobs,CopyLogins" -Source INDCSPSQLA01 -Destination INDCSPSQLP01 -Force
My guess is now aimed at the one time, the job copy happened from the active, secondary node --> primary passive node with -Force. This happened at 21/02 01 AM.
Why is the job step on the passive instance executing on the active instance's database?
On both instances, the job target is local:
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
The servername is correct
select name from sys.servers select @@SERVERNAME
both return the passive server.
The job IDs of active and passive are different:
--08C63F07-0853-41DA-BC88-8FDF44AE491F -- passive --E8C88965-C581-4E06-B651-CC10637FCEEF -- active
Both jobs use the database in question in their step:
--> Should not be accessible on the passive instance, resulting in failure. No readable secondaries
The database is not accessible on the passive instance:
Version of both servers: 14.0.3030.27
T-SQL Job step command example
@subsystem=N'TSQL', @command=N'update statistics dbo.table with fullscan ...'
Nothing is running on the passive instance when the job starts.
Restarting the agent on the passive node 'fixes' this, resulting in a new error on executing:
Unable to connect to SQL Server 'INDCSPSQLP01'. The step failed.
As a result, it no longer updates statistics on the primary instance
SQLAgent - TSQL JobStep (Job 0x9D358B2EF6C53C4BAD6A61CA87D51BF5 : Step 1) SQLAgent - TSQL JobStep (Job 0x6589C8E881C5064EB651CC10637FCEEF : Step 1)