Error: Cannot continue the execution because the session is in the kill state.

Leave a comment

August 24, 2021 by Kenneth Fisher

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.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Enter your email address to follow this blog and receive notifications of new posts by email.

Join 3,569 other followers

Follow me on Twitter

ToadWorld Pro of the Month November 2013
%d bloggers like this: