Troubleshooting Multiserver Jobs

When using multiserver jobs, you may experience one or more of these problems.

The job will not download.

Check the download list at the master server (MSX) either through the user interface or by executing sp_help_downloadlist.

Check the SQL Server Agent error log at the target server (TSX).

After resolving the problem, clear the blocking error at the MSX with the user interface.

There are problems with enlisting or defecting.

• Verify that an operator named MSXOperator exists at the MSX.
• Verify that the MSX is running Mixed Mode Security (SQL Server Authentication and Windows NT Authentication).
• Verify that the MSX is running Microsoft Windows NT, not Microsoft Windows 95 or Windows 98 operating systems.
• Check named pipes connectivity using makepipe at the MSX and readpipe at the TSX.

What if a TSX is reinstalled while it is still enlisted?

The uninstall portion of SQL Server Setup issues a warning that the server should be defected before uninstalling the TSX. If the warning is ignored, or the warning is noted but the MSX is unavailable when the defection occurs, it becomes necessary to resolve the dangling enlistment at the MSX.

To resolve the dangling enlistment

• At the MSX, execute:

EXECUTE sp_delete_targetserver @server_name = '<TSX server name>', 

    @post_defection = 0

What if I need to reinstall msdb on a TSX server?

Either a backup of msdb exists and the msdb database is successfully restored or the msdb database is damaged and no backup is available.

If a backup of msdb is available and msdb is successfully restored, MSX jobs that have been deleted after the msdb backup may also have been restored. To ensure that the deleted jobs are removed, delete all jobs that originated from the MSX and repost the jobs.

To delete and repost jobs

• At the MSX, execute:

  EXECUTE sp_resync_targetserver '<server name>'

  Or

• In SQL Server Enterprise Manager, in the Multi Server Job Execution Status dialog box, click Synchronize Jobs.

If the msdb database is damaged and no backup is available, the TSX must be reenlisted.

To reenlist the TSX

1. At the MSX:

   All jobs that were targeted at the reenlisted TSX must be retargeted using sp_add_jobserver. To generate the Transact-SQL statements to retarget jobs using sp_add_jobserver, execute sp_generate_target_server_job_assignment_sql and save the result set:

   EXECUTE sp_generate_target_server_job_assignment_sql 

   '<TSX server name>'

2. At the TSX, execute:

   EXECUTE sp_msx_enlist '<MSX server name>'

3. At the MSX, execute the previously saved result set of sp_generate_target_server_job_assignment_sql.

What if I need to restore a backup of msdb on the MSX?

If possible, for all TSX servers that have enlisted after the last backup:

1. At the MSX, execute sp_generate_target_server_job_assignment_sql and save the result set:

   EXECUTE sp_generate_target_server_job_assignment_sql 

   '<TSX server name>'

2. At the MSX, restore the msdb database from a backup. Any TSX that has defected after the last msdb backup must be defected manually.

To defect a TSX server manually

• At the MSX, execute:

  EXECUTE sp_delete_targetserver @server_name = '<TSX server name>,     @post_defection = 0

Any TSX that has enlisted after the last msdb backup must then be reenlisted.

To reenlist a TSX that was enlisted after the last msdb backup

1. At the TSX, execute:

   EXECUTE sp_msx_defect

   EXECUTE sp_msx_enlist '<MSX server name>'

2. At the MSX, execute the previously saved result set of sp_generate_target_server_job_assignment_sql.

Note If a defected server is not reenlisted, it will generate many "Incomplete enlistment" errors in the SQL Server Agent error log of the TSX.

If jobs or job assignments have been added, deleted, or changed after the last msdb backup, all remaining TSX servers must be resynchronized.

To resynchronize TSX servers after the last msdb backup

• Execute:

  EXECUTE sp_resync_targetserver 'ALL'

This EXECUTE statement causes each TSX to delete all its MSX jobs and then download them again. This is a very expensive command. An alternative to executing sp_resync_targetserver with the ALL option is to resynchronize the TSX servers manually by selectively posting instructions for all the changes. This approach may work if only a very few changes occurred. However, it is recommended that sp_resync_targetserver with the ALL option be used to resynchronize TSX servers.

What if a TSX computer is renamed while enlisted?

The TSX server can be defected from the TSX side, but must be defected manually from the MSX side. Jobs that were targeted at the TSX must be retargeted by executing sp_add_jobserver. To generate the Transact-SQL statements to retarget jobs using sp_add_jobserver, execute sp_generate_target_server_job_assignment_sql.

1. At the MSX, execute sp_generate_target_server_job_assignment_sql and save the result set:

   EXECUTE sp_generate_target_server_job_assignment_sql 

   '<Old TSX server name>', '<New TSX server name>'

2. At the TSX, execute:

   EXECUTE sp_msx_defect

3. At the MSX, execute:

   EXECUTE sp_delete_targetserver 

   @server_name = '<Old TSX server name>', 

   @post_defection = 0

4. At the TSX, back up the msdb database. Then, uninstall and reinstall SQL Server.

Important Uninstalling and reinstalling SQL Server is necessary because SQL Server does not support renaming of the computer.

5. Restore the backup of the msdb database.
6. At the TSX, execute:

   EXECUTE sp_msx_enlist '<MSX server name>'

7. At the MSX, execute the previously saved result set of sp_generate_target_server_job_assignment_sql.