Index REBUILD doesn't work with SORT_IN_TEMPDB = ON

Question

Index REBUILD doesn't work with SORT_IN_TEMPDB = ON

powelluss

SSC Journeyman

Points: 95
More actions
April 26, 2018 at 4:32 am

#344858

I'm trying to troubleshoot why an index rebuild doesn't work with WITH (SORT_IN_TEMPDB = ON, ONLINE = ON) in Microsoft SQL Server 2016 (SP1-CU3) (KB4019916) - 13.0.4435.0 (X64). I get the following error:
Cannot continue the execution because the session is in the kill state.
I ran DBCC CHECKDB which returned no errors. I have rerun with SORT_IN_TEMPDB = OFF and it seems to work but takes a very long time. I have 86GB free disk space in TEMPDB drive, so surely it can't be disk space issues?
Does anyone have any ideas?

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Luis Cazares SSC Guru Points: 183706 More actions · Answer 1

powelluss - Thursday, April 26, 2018 4:32 AM
I'm trying to troubleshoot why an index rebuild doesn't work with WITH (SORT_IN_TEMPDB = ON, ONLINE = ON) in Microsoft SQL Server 2016 (SP1-CU3) (KB4019916) - 13.0.4435.0 (X64). I get the following error:
Cannot continue the execution because the session is in the kill state.
I ran DBCC CHECKDB which returned no errors. I have rerun with SORT_IN_TEMPDB = OFF and it seems to work but takes a very long time. I have 86GB free disk space in TEMPDB drive, so surely it can't be disk space issues?
Does anyone have any ideas?

Someone might have killed your session.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Evgeny Garaev SSCertifiable Points: 7428 More actions · Answer 2

powelluss - Thursday, April 26, 2018 4:32 AM
Does anyone have any ideas?

May be you have a job which kills sessions that consume too much space in tempdb 😉

powelluss SSC Journeyman Points: 95 More actions · Answer 3

This job runs at 3am when there's very little traffic and no one internally is online.

The job has failed 7 times in a row now, and when I manually tried to run the culprit index rebuild, I got the same error every time. When I run the same rebuild with SORT_IN_TEMPDB = OFF it works?

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

Is there some job that kills sessions that use too much TempDB space? Something another DBA put in place?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass