February 13, 2017 at 1:14 am
I've tried to kill a query but it has the status killed/rollback !
the query is
SELECT Id
FROM dbo.Month WITH ( NOLOCK )
WHERE CONTAINS (( [Title], [Description]), @SearchText )
ORDER BY NewsId DESC
OFFSET ( @PageSize * @PageIndex ) ROWS
FETCH NEXT @PageSize ROWS ONLY;
and wait type is :PWAIT_RESOURCE_SEMAPHORE_FT_PARALLEL_QUERY_SYNC
what should I do ?!
February 13, 2017 at 1:19 am
You can only wait. The rollback has to finish and you can't kill a process that is in the middle of rollback.
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
February 13, 2017 at 1:25 am
this SELECT has nothing to rollback ! and its about 2 days that this query is rolling back !
I had this issue before and It resolve when Sql Server restarts
February 13, 2017 at 1:39 am
Sorry, you are correct. I didn't look at the statement and only read that you killed the process and that the status is killed\rollback. My mistake. Looks like a problem with the full text search, but I haven't got much experience with it. Hope that someone else will be able to help you.
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
February 13, 2017 at 2:28 am
I have seen this before, IIRC, I bounced the Full Text Services with "net stop xxx" and "net start xxx" to resolve it.
😎
You might want to add OPTION(MAXDOP 1) to the query as it doesn't seem to benefit too much from the parallelism.
February 13, 2017 at 5:48 am
Eirikur Eiriksson - Monday, February 13, 2017 2:28 AMI have seen this before, IIRC, I bounced the Full Text Services with "net stop xxx" and "net start xxx" to resolve it.
😎You might want to add OPTION(MAXDOP 1) to the query as it doesn't seem to benefit too much from the parallelism.
I restart MSSQLFDLauncher but process is rolling back yet !
February 13, 2017 at 9:02 am
any other opinion ?
February 13, 2017 at 10:40 am
We had a simlar thing. You should check the SQL Server logs.
In our case, a 3rd party tool incorrectly force closed a connection before SQL was ready so the transaction got stuck. The kill command failed to roll back as it couldn't notify the original program that rollback has completed or even taken place.
This also ended up causing some blocking and slow running queries.
For us, the only solution was to restart the SQL instance. But we only did this after disconnecting the connection between the 3rd party tool and the SQL instance. Otherwise as soon as the instance comes back up, you will have the same issues.
But I'd check the logs for anything related to a minidump.
This is SQL 2016 correct? Is this SP1?
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply