Alternative for KILL

Question

Alternative for KILL

Bhuvnesh

SSC Guru

Points: 59351
More actions
August 16, 2011 at 4:14 am

#246360

HI,
i have to kill a process. i have applied KILL command.
its showing
SPID 98: transaction rollback in progress. Estimated rollback completion: 0%. Estimated time remaining: 0 seconds.
i know it will take time for rollback.but its a heavy transaction.i cant afford to wait for ti.
is there any alternative to make it forcefully KILL ?
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

Viewing 13 posts - 1 through 12 (of 12 total)

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

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

No.

A rollback HAS to complete or your database is structurally and transactionally inconsistent (ie Suspect). There is no way to avoid or abort a rollback.

That 0 seconds, 0% often means the rollback is stuck waiting for something, maybe DTC, maybe a resource in the DB. Check for that.

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

Lowell SSC Guru Points: 323518 More actions · Answer 2

Gail, would disconnecting every connection, as an attempt to clear whatever is blocking that rollback, be helpful at all?

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

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

That's a shotgun to kill a mosquito approach....

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

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 4

GilaMonster (8/16/2011)
That 0 seconds, 0% often means the rollback is stuck waiting for something, maybe DTC, maybe a resource in the DB. Check for that.

How to approach here ? i can take the sql instance at single user mode so that other's work doesnt get affected.

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 5

Additionally server is not behaving good.below is the error i am getting quite often.

SSPI handshake failed with error code 0x80090311 while establishing a connection with integrated security; the connection has been closed. [CLIENT: 10.30.0.129]

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

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

Bhuvnesh (8/16/2011)
GilaMonster (8/16/2011)
That 0 seconds, 0% often means the rollback is stuck waiting for something, maybe DTC, maybe a resource in the DB. Check for that.
How to approach here ? i can take the sql instance at single user mode so that other's work doesnt get affected.

Single user mode not affecting other work???? Single user mode means ONE connection only. I'd say that would affect other work.

If you restart SQL (which is necessary to get it into single user mode), the rollback will continue after the rollback with the database unavailable (unless you have Enterprise Edition)

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

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

Bhuvnesh (8/16/2011)
Additionally server is not behaving good.below is the error i am getting quite often.
SSPI handshake failed with error code 0x80090311 while establishing a connection with integrated security; the connection has been closed. [CLIENT: 10.30.0.129]

That's an AD-related problem.

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

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 8

GilaMonster (8/16/2011)

If you restart SQL (which is necessary to get it into single user mode), the rollback will continue after the rollback with the database unavailable (unless you have Enterprise Edition)

will sql restart help here ? i have enterprise edition .

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

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

Maybe.

The rollback will finish after the restart, it has to. How long it takes is another matter. May be quick, may take hours.

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

ChrisTaylor SSCarpal Tunnel Points: 4527 More actions · Answer 10

what is the process thats running? I've seen similar issues when running bcp / xp_cmdshell and when its killed the process itself doesn't get killed and SQL Server will report the rollback until that exe is stopped.

If the process does run something like above, have a quick look in task manager for bcp.exe or cmd.exe.

_________________________________________________________________________________SQLGeordieWeb:- Jarrin ConsultancyBlog:- www.chrisjarrintaylor.co.ukTwitter:- @SQLGeordie

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 11

Chris, it happened to be same.actually in morning i restarted the sql instance but strangely, i could see same spid still exist there.then i needed to restart the host.i know some people here raise the eye brow on this step but i resolved the issue, because i couldn't afford to keep that spid run

that process was heavy delete operation( which ran accidentally from other server through linked server)

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 12

All processes which are related with "Database Mail" are taking too much time.

Database Engine Instance=IN\DEV;Mail PID=8212;Error Message:
1) Exception Information
===================
Exception Type: Microsoft.SqlServer.Management.SqlIMail.Server.Common.BaseException
Message: The connection is not open.
Data: System.Collections.ListDictionaryInternal
TargetSite: Void ValidateConnection()
HelpLink: NULL
Source: DatabaseMailEngine
StackTrace Information
===================
at Microsoft.SqlServer.Management.SqlIMail.Server.DataAccess.ConnectionManager.ValidateConnection()
at Microsoft.SqlServer.Management.SqlIMail.Server.DataAccess.ConnectionManager.ValidateConnectionAndTransaction()
at Microsoft.SqlServer.Management.SqlIMail.Server.DataAccess.ConnectionManager.RollbackTransaction()
at Microsoft.SqlServer.Management.SqlIMail.IMailProcess.QueueItemProcesser.GetDataFromQueue(DataAccessAdapter da, Int32 lifetimeMinimumSec)
at Microsoft.SqlServer.Management.SqlIMail.IMailProcess.QueueItemProcesser.ProcessQueueItems(String dbName, String dbServerName, Int32 lifetimeMinimumSec, LogLevel loggingLevel)

Additionally there is huge resource consumption by msdb.dbo.sp_readrequest;1

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)