SQL Server 2005 – Fixing Stuck in Single User Mode

I think the code below is what's failing. I got this by running sp_helptext sp_changedistributiondb & also looking at what sproc's call sp_verify_job_identifiers.

It looks like changedistributiondb is having trouble updating the distribution cleanup agent job when it runs sp_update_jobstep :

SELECT @command =  'EXEC dbo.sp_MSdistribution_cleanup @min_distretention = ' + 
                CONVERT(nvarchar(12), @new_min_distretention) + ', @max_distretention = ' +
                CONVERT(nvarchar(12), @max_distretention)
            EXEC @retcode = msdb.dbo.sp_update_jobstep @job_name = @agentname, @step_id = 1, 
                @command = @command

maybe this job has been deleted or there is something else strange going on, either way, you should be able to modify the job step manually.

EDIT: The cleanup agent runs on the server with the distribution database. It removes replication history and runs every 10 minutes by default. The job should look like this:

name: Agent history clean up: distribution
category: REPL-History Cleanup
steps: (1)Run Agent
database: distribution
**command:**EXEC dbo.sp_MShistory_cleanup @history_retention = 48

Might not be the same thing but I was getting that error today.

Turned out that in my haste to grant a user full access to a database I ticked all the boxes under role members including db_denydatareader, db_denydatawriter.