Why are these T-SQL jobs from different SQL Server instances executed on the same instance (AlwaysOn...
Is boss over stepping boundary/micromanaging?
Why did Luke use his left hand to shoot?
Why did the villain in the first Men in Black movie care about Earth's Cockroaches?
Why is working on the same position for more than 15 years not a red flag?
Why do stocks necessarily drop during a recession?
Bash Script Function Return True-False
Does theoretical physics suggest that gravity is the exchange of gravitons or deformation/bending of spacetime?
Can a long polymer chain interact with itself via van der Waals forces?
What is the difference between rolling more dice versus fewer dice?
Positioning node within rectangle Tikz
What is the wife of a henpecked husband called?
Can you tell from a blurry photo if focus was too close or too far?
Porting Linux to another platform requirements
What incentives do banks have to gather up loans into pools (backed by Ginnie Mae)and selling them?
Why avoid shared user accounts?
Do authors have to be politically correct in article-writing?
How can I get my players to come to the game session after agreeing to a date?
What is the purpose of easy combat scenarios that don't need resource expenditure?
Can I string the DnD Starter Set campaign into another modules, keeping the same characters?
Why zero tolerance on nudity in space?
Hybrid flat to drop conversion advice
Can we use the stored gravitational potential energy of a building to produce power?
LuaTex and em dashes
IGBT transistor with auxiliary emitter
Why are these T-SQL jobs from different SQL Server instances executed on the same instance (AlwaysOn Availability Groups)
SQL Server Mirroring kicks in before cluster has time to fail overSQL Server Agent Jobs and Availability GroupsFiles copying to different machine not happening from batch file executed from SQL JobsOn which node first transaction will be executed in Always-on setup?SQL Server 2012 AlwaysOn Availability Group active/passiveScheduled jobs run twice after upgrade to SQL Server 2012How long does a failover for SQL Server Availability Groups take?SQL Agent jobs in Basic Availability Group setupSQL Server 2017 Availability Groups - Can a Long-running transactions on a primary replica block a secondary replica from seeing updates?Load balancing Availability groups with SQL Server Standard
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.
Job history
Before the first failover all was good, and the active server is the only one running the job.
One job running.
We see the stat updates running on the active side. (which is primary replica at the time)
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.
(I killed the sessions).
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.
The Question
Why is the job step on the passive instance executing on the active instance's database?
Checklist
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:
@database_name=N'DB1',
--> 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.
EDIT:
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
Hostnames = Passive & Active server, jobs where visibly running.
Application info
SQLAgent - TSQL JobStep (Job 0x9D358B2EF6C53C4BAD6A61CA87D51BF5 : Step 1)
SQLAgent - TSQL JobStep (Job 0x6589C8E881C5064EB651CC10637FCEEF : Step 1)
sql-server availability-groups sql-server-2017 jobs
edited 13 mins ago
Tony Hinkle
2,7801523
asked 1 hour ago
Randi VertongenRandi Vertongen
2,961721
add a comment |
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.
Job history
Before the first failover all was good, and the active server is the only one running the job.
One job running.
We see the stat updates running on the active side. (which is primary replica at the time)
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.
(I killed the sessions).
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.
The Question
Why is the job step on the passive instance executing on the active instance's database?
Checklist
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:
@database_name=N'DB1',
--> 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.
EDIT:
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
Hostnames = Passive & Active server, jobs where visibly running.
Application info
SQLAgent - TSQL JobStep (Job 0x9D358B2EF6C53C4BAD6A61CA87D51BF5 : Step 1)
SQLAgent - TSQL JobStep (Job 0x6589C8E881C5064EB651CC10637FCEEF : Step 1)
sql-server availability-groups sql-server-2017 jobs
edited 13 mins ago
Tony Hinkle
2,7801523
asked 1 hour ago
Randi VertongenRandi Vertongen
2,961721
Correct me if I'm wrong, but are you saying the job that resides in the msdb database on the secondary is connecting to the availability group-enabled database on the primary?
– Max Vernon
1 hour ago
add a comment |
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.
Job history
Before the first failover all was good, and the active server is the only one running the job.
One job running.
We see the stat updates running on the active side. (which is primary replica at the time)
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.
(I killed the sessions).
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.
The Question
Why is the job step on the passive instance executing on the active instance's database?
Checklist
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:
@database_name=N'DB1',
--> 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.
EDIT:
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
Hostnames = Passive & Active server, jobs where visibly running.
Application info
SQLAgent - TSQL JobStep (Job 0x9D358B2EF6C53C4BAD6A61CA87D51BF5 : Step 1)
SQLAgent - TSQL JobStep (Job 0x6589C8E881C5064EB651CC10637FCEEF : Step 1)
sql-server availability-groups sql-server-2017 jobs
edited 13 mins ago
Tony Hinkle
2,7801523
asked 1 hour ago
Randi VertongenRandi Vertongen
2,961721
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.
Job history
Before the first failover all was good, and the active server is the only one running the job.
One job running.
We see the stat updates running on the active side. (which is primary replica at the time)
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.
(I killed the sessions).
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.
The Question
Why is the job step on the passive instance executing on the active instance's database?
Checklist
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:
@database_name=N'DB1',
--> 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.
EDIT:
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
Hostnames = Passive & Active server, jobs where visibly running.
Application info
SQLAgent - TSQL JobStep (Job 0x9D358B2EF6C53C4BAD6A61CA87D51BF5 : Step 1)
SQLAgent - TSQL JobStep (Job 0x6589C8E881C5064EB651CC10637FCEEF : Step 1)
sql-server availability-groups sql-server-2017 jobs
sql-server availability-groups sql-server-2017 jobs
edited 13 mins ago
Tony Hinkle
2,7801523
asked 1 hour ago
Randi VertongenRandi Vertongen
2,961721
edited 13 mins ago
Tony Hinkle
2,7801523
asked 1 hour ago
Randi VertongenRandi Vertongen
2,961721
edited 13 mins ago
Tony Hinkle
2,7801523
edited 13 mins ago
Tony Hinkle
2,7801523
edited 13 mins ago
Tony Hinkle
2,7801523
2,7801523
asked 1 hour ago
Randi VertongenRandi Vertongen
2,961721
asked 1 hour ago
Randi VertongenRandi Vertongen
2,961721
asked 1 hour ago
Randi VertongenRandi Vertongen
2,961721
2,961721
Correct me if I'm wrong, but are you saying the job that resides in the msdb database on the secondary is connecting to the availability group-enabled database on the primary?
– Max Vernon
1 hour ago
add a comment |
Correct me if I'm wrong, but are you saying the job that resides in the msdb database on the secondary is connecting to the availability group-enabled database on the primary?
– Max Vernon
1 hour ago
Correct me if I'm wrong, but are you saying the job that resides in the msdb database on the secondary is connecting to the availability group-enabled database on the primary?
– Max Vernon
1 hour ago
Correct me if I'm wrong, but are you saying the job that resides in the msdb database on the secondary is connecting to the availability group-enabled database on the primary?
– Max Vernon
1 hour ago
add a comment |
1 Answer
1
active
oldest
votes
I don't have a diagnosis for why this problem occurred, but if you have jobs running on databases that are in availability groups, it's best to include a check in step 1 that uses the fn_hadr_is_primary_replica function to check whether it is running on the primary or secondary.
IF (sys.fn_hadr_is_primary_replica('DB1') <> 1)
BEGIN
RAISERROR('%s is secondary', 11, 1, @@servername );
END
Configure this step to quit the job on failure. This is better than trying to run something that fails because it's hitting a secondary.
answered 55 mins ago
Tony HinkleTony Hinkle
2,7801523
I do understand that a clear reason why is probably not possible, especially now that I restarted the agent and I cannot run the job anymore. If nobody else gives a plausible explanation i will accept this one as the answer, thanks again!
– Randi Vertongen
2 mins ago
add a comment |
Your Answer
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "182"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f230953%2fwhy-are-these-t-sql-jobs-from-different-sql-server-instances-executed-on-the-sam%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
I don't have a diagnosis for why this problem occurred, but if you have jobs running on databases that are in availability groups, it's best to include a check in step 1 that uses the fn_hadr_is_primary_replica function to check whether it is running on the primary or secondary.
IF (sys.fn_hadr_is_primary_replica('DB1') <> 1)
BEGIN
RAISERROR('%s is secondary', 11, 1, @@servername );
END
Configure this step to quit the job on failure. This is better than trying to run something that fails because it's hitting a secondary.
answered 55 mins ago
Tony HinkleTony Hinkle
2,7801523
I do understand that a clear reason why is probably not possible, especially now that I restarted the agent and I cannot run the job anymore. If nobody else gives a plausible explanation i will accept this one as the answer, thanks again!
– Randi Vertongen
2 mins ago
add a comment |
I don't have a diagnosis for why this problem occurred, but if you have jobs running on databases that are in availability groups, it's best to include a check in step 1 that uses the fn_hadr_is_primary_replica function to check whether it is running on the primary or secondary.
IF (sys.fn_hadr_is_primary_replica('DB1') <> 1)
BEGIN
RAISERROR('%s is secondary', 11, 1, @@servername );
END
Configure this step to quit the job on failure. This is better than trying to run something that fails because it's hitting a secondary.
answered 55 mins ago
Tony HinkleTony Hinkle
2,7801523
I do understand that a clear reason why is probably not possible, especially now that I restarted the agent and I cannot run the job anymore. If nobody else gives a plausible explanation i will accept this one as the answer, thanks again!
– Randi Vertongen
2 mins ago
add a comment |
I don't have a diagnosis for why this problem occurred, but if you have jobs running on databases that are in availability groups, it's best to include a check in step 1 that uses the fn_hadr_is_primary_replica function to check whether it is running on the primary or secondary.
IF (sys.fn_hadr_is_primary_replica('DB1') <> 1)
BEGIN
RAISERROR('%s is secondary', 11, 1, @@servername );
END
Configure this step to quit the job on failure. This is better than trying to run something that fails because it's hitting a secondary.
answered 55 mins ago
Tony HinkleTony Hinkle
2,7801523
I don't have a diagnosis for why this problem occurred, but if you have jobs running on databases that are in availability groups, it's best to include a check in step 1 that uses the fn_hadr_is_primary_replica function to check whether it is running on the primary or secondary.
IF (sys.fn_hadr_is_primary_replica('DB1') <> 1)
BEGIN
RAISERROR('%s is secondary', 11, 1, @@servername );
END
Configure this step to quit the job on failure. This is better than trying to run something that fails because it's hitting a secondary.
answered 55 mins ago
Tony HinkleTony Hinkle
2,7801523
answered 55 mins ago
Tony HinkleTony Hinkle
2,7801523
answered 55 mins ago
Tony HinkleTony Hinkle
2,7801523
answered 55 mins ago
Tony HinkleTony Hinkle
2,7801523
2,7801523
I do understand that a clear reason why is probably not possible, especially now that I restarted the agent and I cannot run the job anymore. If nobody else gives a plausible explanation i will accept this one as the answer, thanks again!
– Randi Vertongen
2 mins ago
add a comment |
I do understand that a clear reason why is probably not possible, especially now that I restarted the agent and I cannot run the job anymore. If nobody else gives a plausible explanation i will accept this one as the answer, thanks again!
– Randi Vertongen
2 mins ago
I do understand that a clear reason why is probably not possible, especially now that I restarted the agent and I cannot run the job anymore. If nobody else gives a plausible explanation i will accept this one as the answer, thanks again!
– Randi Vertongen
2 mins ago
I do understand that a clear reason why is probably not possible, especially now that I restarted the agent and I cannot run the job anymore. If nobody else gives a plausible explanation i will accept this one as the answer, thanks again!
– Randi Vertongen
2 mins ago
add a comment |
Thanks for contributing an answer to Database Administrators Stack Exchange!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f230953%2fwhy-are-these-t-sql-jobs-from-different-sql-server-instances-executed-on-the-sam%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Correct me if I'm wrong, but are you saying the job that resides in the msdb database on the secondary is connecting to the availability group-enabled database on the primary?
– Max Vernon
1 hour ago