I'd say the easiest possible way to do this is through PowerShell and SMO. Take the following code for instance:
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") |
Out-Null
$SqlServerNames = "Server1", "Server2"
$SqlJobName = "YourJob"
foreach ($SqlServerName in $SqlServerNames) {
$SqlServer = New-Object Microsoft.SqlServer.Management.Smo.Server($SqlServerName)
$SqlServer.JobServer.Jobs[$SqlJobName].Start()
}
All this does it loop through a list of servers (explicitly stated at as string array variable, but can easily be obtained also from a SQL Server database table, a text file, etc.), connects to the current server, and then starts the job with the job named whatever you set the $SqlJobName
variable to.
It's also worth noting that the Start()
function will not wait for the job to complete before continuing code execution.
As you can see, in very few lines of PowerShell code you are able to accomplish this task. But I'd take it a step further, such as error handling. You don't want this to bomb out and not know what failed, when it failed, and what servers it did/didn't run on.
foreach ($SqlServerName in $SqlServerNames) {
try {
$SqlServer = New-Object Microsoft.SqlServer.Management.Smo.Server($SqlServerName)
$SqlServer.JobServer.Jobs[$SqlJobName].Start()
}
catch {
# add your error handling code here
## for instance, write the error to a text file
}
}
And, as always with any code, test this out in a non-production environment to ensure that it does what you think it will and should do. Re-work it for your needs/environment.
As per RoKa's comment:
Can this be edited to check for whether the job is currently running, error handling?
Great point, and definitely something to check for (as well as if the job actually exists by checking for a non-null value):
foreach ($SqlServerName in $SqlServerNames) {
try {
$SqlServer = New-Object Microsoft.SqlServer.Management.Smo.Server($SqlServerName)
$SqlJob = $SqlServer.JobServer.Jobs[$SqlJobName]
if (($SqlJob) -and ($SqlJob.CurrentRunStatus -eq [Microsoft.SqlServer.Management.Smo.Agent.JobExecutionStatus]::Idle)) {
$SqlJob.Start()
}
else {
# handle/log accordingly if the job doesn't exist or it's not idle
}
}
catch {
# add your error handling code here
## for instance, write the error to a text file
}
}
EDIT: While re-reading your question, I see SQL Server 2000. Are those going to be the target instances? If that is the case, and you do decide to go with the PowerShell/SMO answer I provided, then definitely definitely definitely test and ensure it is going to do what you want. Test this out very far from production and be 100% sure it does what you think it will.
EDIT: It looks like SMO versions before SQL Server 2012 (i.e. 2005, 2008, and 2008 R2) support managing SQL Server 2000 instances. I'm finding this information through this BOL reference.
Using Kenneth's code, and a few other bits and pieces that I pulled together, I came up with the following code which I think should do the job. If anybody sees any potential problem with this I'd be interested in knowing!
-- Check if this server is the preferred replica for backups.
If sys.fn_hadr_backup_is_preferred_replica( @DatabaseName ) <> 1
BEGIN
-- Check whether the script is running from SQL Agent. If it is not then raise an error so that the user knows that the backup hasn't completed.
-- (If the job is run from the agent then there should be an equivalent job running on the preferred replica, so no need to alert).
declare @AgentAccount varchar(100)
set @AgentAccount = (select cast(value_data as varchar(100)) as [AccountName]
FROM sys.dm_server_registry
WHERE registry_key LIKE '%SQLSERVERAGENT%' --For named instance change to SQLAgent$<instanceName>
AND value_name = 'ObjectName'
)
if @AgentAccount = SYSTEM_USER
begin
declare @jobSource tinyint
SELECT @jobSource = ja.run_requested_source
--WHEN 1 THEN 'SOURCE_SCHEDULER'
--WHEN 2 THEN 'SOURCE_ALERTER'
--WHEN 3 THEN 'SOURCE_BOOT'
--WHEN 4 THEN 'SOURCE_USER' -- This one is manual
--WHEN 6 THEN 'SOURCE_ON_IDLE_SCHEDULE'
--ELSE 'UNKNOWN' END
FROM msdb.dbo.sysjobactivity ja
WHERE ja.session_id = @@SPID
AND start_execution_date is not null
AND stop_execution_date is null;
if @jobSource = 4 -- Job was run manually.
begin
RAISERROR ('No backup produced as this server is not the preferred replica for backups. Please run the script again from the preferred replica.', 16, 1)
return 1
end
else
begin
print 'No backup produced as this server is not the preferred replica for backups.'
return 0
end
end
else
begin
RAISERROR ('No backup produced as this server is not the preferred replica for backups. Please run the script again from the preferred replica.', 16, 1)
return 1
end
END
Best Answer
Looks like the problem is exactly in the description - the job is already running.
Try stopping the job first, then re-running it. Either with the GUI, or
Other things to check: What is the job doing? Can you do that (run the script, for example) directly (not using Agent)?
Has the job got elements (e.g. a script) which can never actually finish, therefore the job is never going to stop (and therefore will not start again on schedule).
It's a bit hacky, but you can schedule a job-killing job to stop any jobs that are over-running (although it's usually better to try fix your jobs so they finish properly).
This script from SQL Server Central provides some handy info about currently running jobs:
That should help you sort out your problem, if not, post some more details about what exactly the job is doing.
Edit- Code to check SQL server agent is running (run from Management studio). DECLARE @agent NVARCHAR(512);
You can manage Agent from SQL Server Configuration Manager.
Ian