• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 249
  • Last Modified:

How can I kill a process in SQL 2005 that I own

I have a situation where another DBA was using the production server to test a job A modification. Job A was not killed properly. In my zeal to unlock the server, I deleted the job B by  mistake. Job B had several processes open and one of them has me as the owner.  Since I am the owner I cannot kill the process. I tried stopping and re-starting SQLAgent.  I also stopped and restarted the database.  The process is still running. Is my only recourse to reboot the server?
0
RosalindP
Asked:
RosalindP
  • 4
  • 3
1 Solution
 
chapmandewCommented:
you mean you stopped and restarted the instance?  the process is likely doing cleanup work if that is the case...you'll have to wait for it to rollback.
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
i agree with tim.. try running sp_who2 and check tyhe status
0
 
RosalindPAuthor Commented:
Yes, I stopped and started the instance.  When I ran a select against sys.dm_tran_active_transactions the transaction_state and transaction_status were both 2.  The dtc_state was 0.  Shouldn't the dtc_state be something else if the process is doing cleanup work?
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
RosalindPAuthor Commented:
When I look at the properties of process from the Activity Monitor it says:

create table #tmpDBCCinputbuffer ([Event Type] nvarchar(512), [Parameters] int, [Event Info] nvarchar(512))
insert into #tmpDBCCinputbuffer exec ('DBCC INPUTBUFFER(51)')
select [Event Info] from #tmpDBCCinputbuffer

The status of the process is runnable.
0
 
chapmandewCommented:
if you restarted the instance, then the process was killed.  It is rolling back...for sure.

It is still an active transaction, but it is rolling backup..which is why the state of 2.  Not sure the dtc state really has much to do with this scenario.
0
 
RosalindPAuthor Commented:
Thank you.  I'll just wait for the rollback to occur and then award the points.
0
 
chapmandewCommented:
bad thing is, you can never tell when the rollback is going to be finished.
0
 
RosalindPAuthor Commented:
chapmandew responses where extremely helpful and I was to understand more of how transactions work.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now