Sql Server - Running a Job on Multiple servers

I am using SqlServer 2008.

I have a Sql Server Job that checks Disk Space and emails admins if the space is less than 15%

Instead of adding that Job to all servers is it Possible to run the Job on all servers ?

(I have those servers added as Linked Servers)

To answer the specific question of running a SQL Agent job on multiple servers (definitely a good question) and, for the moment, ignoring the particular thing that you want the job to do (which could be another question):

SQL Server / SQL Server Agent actually comes with a facility that makes this type of thing very easy: multiserver administration. You set up a Master (MSX) and several Target (TSX) servers. You then create the job only on the Master and then select which of the Target servers it should run on.

Please see the section of MSDN pages, starting with Automated Administration Across an Enterprise.

It is actually quite nice. I worked at a place with 50 (at least) instances in Product, plus 5 other environments (Dev, QA, etc) with something like 12 - 30 instance per environment. Lots of SQL Agent jobs, mostly managed via MSX / TSX.

You can still create jobs local to any particular instance. For example, when migrating data from a large table to a new structure (a pre-release task for minimizing downtime so that during the release the tables just need to be swapped), about 1 week prior to the release I would create the new tables and local jobs to migrate batches of 1k - 4k rows, every few minutes. Creating them as local instead of MSX allowed for:

• Setting different batch sizes and schedules per node: We had 20 nodes and some were either quite a bit larger than the rest or maybe quite a bit smaller. Different batch sizes worked better for different data volumes. Along those same lines, as the jobs working on the nodes with larger tables got to maybe 30% complete or something, they started to slow down, requiring minor changes to batch size and/or schedule frequency. It is not possible with MSX (without a large CASE @@SERVERNAME statement) to tailor job specifics per Target / TSX server.

• Auto-clean up: The very nature of these jobs was temporary, and once all of the data on a node had been migrated, there was no more purpose in that partcular job as new rows can be handled via the INSERT, UPDATE, DELETE trigger that kept the already migrated rows in sync. Hence, once the job detected that 0 rows were moved, it looked up its own JobID and called [msdb].dbo.sp_delete_job to delete itself. Doing this meant that we didn't have jobs running that weren't doing any work, and we didn't have to worry about job cleanup during or after the release. I do not think this is possible with MSX / TSX as I do not think a TSX server can delist one of its MSX jobs. Of course, the documentation for sp_delete_job doesn't say that it can't and I haven't tried.

And Linked Servers aren't required for any of the MSX / TSX communication. In fact, you should be careful when restoring a backup of an MSX server to another server for the purposes of testing. I did that one time with our Product MSX server and started removing some of the Target server meta-data as it was cluttering up what I was trying to test. A few hours later the lead DBA sent out an urgent message asking who was messing with the Jobs in Production as quite a few jobs were being mysteriously delisted from some / most / all of their TSX servers. I just happened to be that the server I was using for testing was sitting inside the Production network for obsolete technical reasons and was allowed to communicate with the TSX nodes. Oops ;-). Needless to say, Mr Lead DBA was something quite-the-opposite-of-happy with me (and the junior DBA who did the restore for me).

To be clear about the behavior, when you select a Target server, the job and its related data (steps, schedule(s), etc) are replicated to that TSX server. So the job will live on the Target server and run via SQL Agent on that Target server; the jobs are not running on the Master server.

If you have ever wondered what the [originating_server_id] field was in [msdb].dbo.sysjobs, it refers to the Master/MSX server (I believe a value of 0 means a "local" job).

SELECT * FROM [msdb].dbo.sysjobs -- originating_server_id

Also:

-- map of jobs and what Target/TSX servers they are on
SELECT * FROM [msdb].dbo.sysjobservers 

-- registered TSX servers
SELECT * FROM [msdb].dbo.systargetservers

Here is the full list of SQL Server Agent Tables

StackExchange Database Administrators Q#90671, answer score: 8