This was a rather interesting error. I’d certainly never seen it before, and based on one of the answers to this Stack Overflow question it appears it can happen for any number of reasons. Personally I like the way I ended up with it because I think it pretty well encapsulated what’s going on.
We had one job start another job. We’ll call the first job JobStart. The job it’s calling is a log shipping job so we’ll call it LogShipping.
JobStart‘s first step looks something like this.
USE DB1;
GO
EXEC msdb.dbo.sp_start_job 'LogShipping';
GO
-- More code here.
The first step of LogShipping kills all connections to DB1. Including the connection from StartJob.
If all JobStart had done was start LogShipping this wouldn’t be a big issue since SQL Agent Jobs are asynchronous and JobStart would already be done. However we have more code after the job started which, among other things, checked to see if LogShipping was still running. (I can post code for that if anyone is interested.)
So at this point JobStart is still trying to work, but no longer has a connection. (It was killed by LogShipping, remember?) This causes the error above, which again, made perfect sense to me.
And of course the solution is simple enough. We changed the database that JobStart was connecting to.
USE msdb;
GO
EXEC msdb.dbo.sp_start_job 'Job2';
GO
Now when LogShipping kills all of the connections to DB1 the StartJob job is unaffected. FYI, don’t ask me why it was set up that way initially, I’m not really sure.