PRB: Expired Subscription Cleanup Agent Fails if the First Published Database is Deleted

If the first database that is published is then unpublished and dropped, and there are databases that are published, the Expired Subscription Cleanup Agent fails and the following error message occurs: The error is also reported in the SQLServerAgent error log:

The Expired Subscription Cleanup job executes the sp_expired_subscription_cleanup stored procedure within the context of the first database that was published. The sp_expired_subscription_cleanup stored procedure tries to run under the context of the database that was dropped but the procedure fails.

To work around this problem, follow these steps:

1. Open the SQL Enterprise Manager, and then navigate to the server that has the failed job.
2. Open the Miscellaneous Agents folder under the Replication Monitor folder. In the details window right-click the Expired Subscription Cleanup Agent, and then click Agent Properties.
3. To open the edit job step window, click the Steps tab, and then double-click the Run agent step.
4. Under the database option, you will see the first database (in alphabetical order) to be the database context under which the agent executes the stored procedure.
5. Select an existing database (preferably choose the master), and then click OK.
6. Close all the open windows. Click OK, and then start the agent. The agent now runs successfully.