Question regarding a small Kill Command? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Question regarding a small Kill Command?

Author

Topic

zeeshan13
Constraint Violating Yak Guru

347 Posts

Posted - 2010-08-30 : 14:11:25

Hi All,

Our client use our application. The application is hanging currently and we dont know yet why. I do see a SQL Managment Studio 2005 query analyzer open with a kill query which has already executed. Please see attached image. We know it is ran by one of our anaylst who is currently on vacation. I have been asked to investigate if this KILL query has any impact on application which is hanging. Please see the attched image at the following website.

[url]http://www.flickr.com/photos/7714646@N07/4942007493/sizes/l/in/photostream/[/url]

Any idea what this KILL query has done? How I can investigate the impact of this query? Please suggest.

Thanks a lot.

Zee

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-08-30 : 14:31:40

Run these statements and post back here:
KILL 55 WITH STATUSONLY

DBCC INPUTBUFFER(55)

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-08-30 : 14:31:45

There was a transaction running and I think it takes too long (too much time) so someone has tried to kill the session with that running transaction.
But the killed transaction is doing a rollback. The rollback can as much time as the transaction has needed before the kill.

To see if the rollback is making progress you can do:
KILL 55 WITH STATUSONLY

No, you're never too old to Yak'n'Roll if you're too young to die.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-08-30 : 14:32:16

5 seconds!

No, you're never too old to Yak'n'Roll if you're too young to die.

zeeshan13
Constraint Violating Yak Guru

347 Posts

Posted - 2010-08-30 : 14:40:55

Everyone above,

Before I run these statements let me ask this: Is the query still running? I thought its not? Does the following statement has any impact on the database/application?

KILL 55 WITH STATUSONLY

DBCC INPUTBUFFER(55)

Please suggest.

Thanks,

Zee

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-08-30 : 14:49:15

Please post the output of the queries I posted in order to answer your questions.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-08-30 : 14:51:33

You can do what Tara suggested without any danger.

No, you're never too old to Yak'n'Roll if you're too young to die.

zeeshan13
Constraint Violating Yak Guru

347 Posts

Posted - 2010-08-30 : 15:06:33

Ok Guys. I ran the following two queries and I got the following msgs.

KILL 55 WITH STATUSONLY
--Message:
SPID 55: transaction rollback in progress. Estimated rollback completion: 0%. Estimated time remaining: 0 seconds.

DBCC INPUTBUFFER(55)

--MSG:

(Column names are seperated by comma):
EventType, Parameters, EventInfo

(Values are seperated by comma):
Language Event, 0, delete from competitorinfo where entitytype = 'PR' and date < '2010-07-14'

When I run the following query it takes forever and keep on running:
select count(*) from competitorinfo where entitytype = 'PR' and date < '2010-07-14'

Any conclusion to above messages? Please suggest.

Thanks.....

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-08-30 : 15:19:01

Run this:

sp_lock 55

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-08-30 : 15:19:22

And this:

select * from master..sysprocesses where spid = 55

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

zeeshan13
Constraint Violating Yak Guru

347 Posts

Posted - 2010-08-30 : 15:33:35

Was waiting for a reply here and my team was pushing me hard to I restarted the SQL server. It keep on saying the following.

Attempting to stop the following service on SPAR...

*SPAR is the server name.

What should I do know?

Please suggest promptly.

zeeshan13
Constraint Violating Yak Guru

347 Posts

Posted - 2010-08-30 : 15:39:34

Again it just keep on saying the following.

Attempting to stop the following service on SPAR...

*SPAR is the server name.

What should I do now?

Guys please suggest promptly. Thanks a million for your help.

Zee

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-08-30 : 15:40:48

Please remember that we are offering free advice and on our own time. If your issue is urgent, then you need to open a case with Microsoft PSS.

What is the current state of the service? What does the Application Log in Event Viewer show?

By restarting the SQL Server, you will cause it to go into crash recovery. Crash recovery could take minutes/hours/days to complete if a large transaction needs to be rolled back. If it takes longer than you can afford to be down, then you need to consider restoring from backups. You can view the progress of crash recovery in the Application Log in Event Viewer or in the SQL Server Error Log.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

zeeshan13
Constraint Violating Yak Guru

347 Posts

Posted - 2010-08-30 : 15:56:16

Sorry tkizer.

Actually someone else suggested me to restart SQL. But based on what you are telling me he gave the wrong suggestion.

The latest application logs in event viewer has the same log reparting which says the following

Event Type: Information
Event Source: MSSQLSERVER
Event Category: (2)
Event ID: 851
Date: 8/30/2010
Time: 3:45:43 PM
User: SPAR\K
Computer: SPAR
Description:
The operating system returned error 1450(Insufficient system resources exist to complete the requested service.) to SQL Server during a write at offset 0x000000a195e000 in file with handle 0x0000000000001654. This is usually a temporary condition and the SQL Server will keep retrying the operation. If the condition persists then immediate action must be taken to correct it.

For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.
Data:
0000: 53 03 00 00 0a 00 00 00 S.......
0008: 0a 00 00 00 47 00 52 00 ....G.R.
0010: 4d 00 4b 00 53 00 53 00 M.K.S.S.
0018: 44 00 42 00 50 00 00 00 D.B.P...
0020: 0b 00 00 00 50 00 72 00 ....P.r.
0028: 69 00 63 00 65 00 53 00 i.c.e.S.
0030: 74 00 72 00 61 00 74 00 t.r.a.t.
0038: 00 00 ..

What should I do now? Any suggestions please.

In case you need to know this how the disk space of the server looks like (please follow the link below).

http://www.flickr.com/photos/7714646@N07/4942347617/sizes/l/in/photostream/

Thanks a load !

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2010-08-30 : 15:59:39

nothing much you can do now other than wait for it to finish recovering -- other than what Tara already said.

a little too late now, but never restart sql because a rollback is taking too long

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-08-30 : 16:01:13

I am not familiar with that one when shutting down SQL Server. It sounds like you've got memory issues though.

If the service hasn't yet stopped, then you should be to connect to it and run the additional queries I posted.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

zeeshan13
Constraint Violating Yak Guru

347 Posts

Posted - 2010-08-30 : 16:32:25

tkizer,

I ran your queries and I got following results.

select * from master..sysprocesses where spid = 55
Column name (seperated by comma):

spid,kpid,blocked, waittype,waittime,lastwaittype, waitresource, dbid, uid, cpu, physical_io, memusage, login_time,
last_batch, ecid, open_tran, status, sid, hostname, program_name, hostprocess, cmd, nt_domain, nt_username, net_address,
net_library, loginname, context_info, sql_handle, stmt_start, stmt_end, request_id

Result set (Seperated by comma):

55,800,0,0x0000,0,IO_COMPLETION , ,7,1,8335044,465686928,2,40419.4094420139,40420.0575508449,0,2,runnable ,0x010500000000000515000000F094C85F27C7F56D07E53B2B652A000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000,GRMKSSDBP ,Microsoft SQL Server Management Studio - Query ,3792,KILLED/ROLLBACK ,SPARTANSTORE,KSSVPN1,00215E2319DC,LPC ,SPARTANSTORE\KSSVPN1 ,0x0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000,0x010007008D943536009F334F0800000000000000,0,-1,0
55,4696,0,0x00BA,48631484,CXPACKET , ,7,1,28859,892316,0,40419.4094420139,40420.0575508449,1,0,suspended ,0x010500000000000515000000F094C85F27C7F56D07E53B2B652A000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000,GRMKSSDBP ,Microsoft SQL Server Management Studio - Query ,3792,KILLED/ROLLBACK ,SPARTANSTORE,KSSVPN1,00215E2319DC,LPC , ,0x0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000,0x0000000000000000000000000000000000000000,0,-1,0
55,4444,0,0x00BA,48667796,CXPACKET , ,7,1,29328,904946,0,40419.4094420139,40420.0575508449,3,0,suspended ,0x010500000000000515000000F094C85F27C7F56D07E53B2B652A000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000,GRMKSSDBP ,Microsoft SQL Server Management Studio - Query ,3792,KILLED/ROLLBACK ,SPARTANSTORE,KSSVPN1,00215E2319DC,LPC , ,0x0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000,0x0000000000000000000000000000000000000000,0,-1,0
55,3152,0,0x00BA,48706453,CXPACKET , ,7,1,28125,888058,0,40419.4094420139,40420.0575508449,2,0,suspended ,0x010500000000000515000000F094C85F27C7F56D07E53B2B652A000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000,GRMKSSDBP ,Microsoft SQL Server Management Studio - Query ,3792,KILLED/ROLLBACK ,SPARTANSTORE,KSSVPN1,00215E2319DC,LPC , ,0x0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000,0x0000000000000000000000000000000000000000,0,-1,0
55,4256,0,0x00BA,48748000,CXPACKET , ,7,1,28875,899061,0,40419.4094420139,40420.0575508449,7,0,suspended ,0x010500000000000515000000F094C85F27C7F56D07E53B2B652A000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000,GRMKSSDBP ,Microsoft SQL Server Management Studio - Query ,3792,KILLED/ROLLBACK ,SPARTANSTORE,KSSVPN1,00215E2319DC,LPC , ,0x0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000,0x0000000000000000000000000000000000000000,0,-1,0
55,3924,0,0x00BA,48586578,CXPACKET , ,7,1,29875,902043,0,40419.4094420139,40420.0575508449,6,0,suspended ,0x010500000000000515000000F094C85F27C7F56D07E53B2B652A000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000,GRMKSSDBP ,Microsoft SQL Server Management Studio - Query ,3792,KILLED/ROLLBACK ,SPARTANSTORE,KSSVPN1,00215E2319DC,LPC , ,0x0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000,0x0000000000000000000000000000000000000000,0,-1,0
55,3132,0,0x00BA,48539203,CXPACKET , ,7,1,27734,899187,0,40419.4094420139,40420.0575508449,4,0,suspended ,0x010500000000000515000000F094C85F27C7F56D07E53B2B652A000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000,GRMKSSDBP ,Microsoft SQL Server Management Studio - Query ,3792,KILLED/ROLLBACK ,SPARTANSTORE,KSSVPN1,00215E2319DC,LPC , ,0x0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000,0x0000000000000000000000000000000000000000,0,-1,0
55,3348,0,0x00BA,48475250,CXPACKET , ,7,1,29860,908414,0,40419.4094420139,40420.0575508449,5,0,suspended ,0x010500000000000515000000F094C85F27C7F56D07E53B2B652A000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000,GRMKSSDBP ,Microsoft SQL Server Management Studio - Query ,3792,KILLED/ROLLBACK ,SPARTANSTORE,KSSVPN1,00215E2319DC,LPC , ,0x0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000,0x0000000000000000000000000000000000000000,0,-1,0
55,4684,0,0x00BA,48490453,CXPACKET , ,7,1,30438,914453,0,40419.4094420139,40420.0575508449,8,0,suspended ,0x010500000000000515000000F094C85F27C7F56D07E53B2B652A000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000,GRMKSSDBP ,Microsoft SQL Server Management Studio - Query ,3792,KILLED/ROLLBACK ,SPARTANSTORE,KSSVPN1,00215E2319DC,LPC , ,0x0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000,0x0000000000000000000000000000000000000000,0,-1,0

zeeshan13
Constraint Violating Yak Guru

347 Posts

Posted - 2010-08-30 : 16:39:21

tkizer,

I ran your another query and got following results.

sp_lock 55
Column name (seperated by comma):
spid, dbid, ObjId, IndId, Type, Resource, Mode, Status

Result set (seperated by comma):

55,7,0,0,DB, ,S,GRANT
55,7,629577281,0,TAB, ,X,GRANT
55,7,629577281,0,TAB, ,X,GRANT
55,7,629577281,0,TAB, ,X,GRANT
55,7,629577281,0,TAB, ,X,GRANT
55,7,629577281,0,TAB, ,X,GRANT
55,7,629577281,0,TAB, ,X,GRANT
55,7,629577281,0,TAB, ,X,GRANT
55,7,629577281,0,TAB, ,X,GRANT
55,7,629577281,0,TAB, ,X,GRANT
55,7,629577281,0,TAB, ,IX,GRANT

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-08-30 : 16:39:50

I don't see the login time for this spid, but maybe it's not available.

It seems that the analyst's kill is stalled. We had this issue back in April and opened a high priority case with Microsoft in the middle of the night. They found that we were completely stalled on IO. They had us restart the service. Crash recovery took about 5 minutes that time, and we've since replaced the SAN.

If the service is unable to be stopped, then you should hard boot the server. Please note that I take no responsibility if this doesn't come back up cleanly as you appear to be having hardware issues. Make sure you have good backups.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-08-30 : 16:40:40

Also, please edit your posts to add more carriage returns as the code tags have made this topic too wide. It is very hard to read now that we have to scroll to the right.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

zeeshan13
Constraint Violating Yak Guru

347 Posts

Posted - 2010-08-30 : 16:50:29

tkizer,

What do you mean by "analyst's kill"? What is that? Please exaplain.

What do you mean when you say "completely stalled on IO" Can you please explain?

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources