August 3, 2011 at 12:27 pm
Hi,
I have a SQL Production server where I killed 4 processes but they still appear in the activity monitor with a status as KILLED/ROLLBACK.
Can anyone tell me how to remove them permanently without restarting the instance and does it have any affect on the instance ?
Thank you in advance,
Sree
August 3, 2011 at 12:29 pm
How long have they been working since the kill?
What were they doing before the kill and for how long.
What does this return (waiting info).
--Borrowed from the activity monitor
SELECT
[Session ID] = s.session_id,
[User Process] = CONVERT(CHAR(1), s.is_user_process),
[Login] = s.login_name,
[Database] = ISNULL(db_name(p.dbid), N''),
[Task State] = ISNULL(t.task_state, N''),
[Command] = ISNULL(r.command, N''),
[Application] = ISNULL(s.program_name, N''),
[Wait Time (ms)] = ISNULL(w.wait_duration_ms, 0),
[Wait Type] = ISNULL(w.wait_type, N''),
[Wait Resource] = ISNULL(w.resource_description, N''),
[Blocked By] = ISNULL(CONVERT (varchar, w.blocking_session_id), ''),
[Head Blocker] =
CASE
-- session has an active request, is blocked, but is blocking others or session is idle but has an open tran and is blocking others
WHEN r2.session_id IS NOT NULL AND (r.blocking_session_id = 0 OR r.session_id IS NULL) THEN '1'
-- session is either not blocking someone, or is blocking someone but is blocked by another party
ELSE ''
END,
[Total CPU (ms)] = s.cpu_time,
[Total Physical I/O (MB)] = (s.reads + s.writes) * 8 / 1024,
[Memory Use (KB)] = s.memory_usage * 8192 / 1024,
[Open Transactions] = ISNULL(r.open_transaction_count,0),
[Login Time] = s.login_time,
[Last Request Start Time] = s.last_request_start_time,
[Host Name] = ISNULL(s.host_name, N''),
[Net Address] = ISNULL(c.client_net_address, N''),
[Execution Context ID] = ISNULL(t.exec_context_id, 0),
[Request ID] = ISNULL(r.request_id, 0)
-- [Workload Group] = ISNULL(g.name, N'')
FROM sys.dm_exec_sessions s LEFT OUTER JOIN sys.dm_exec_connections c ON (s.session_id = c.session_id)
LEFT OUTER JOIN sys.dm_exec_requests r ON (s.session_id = r.session_id)
LEFT OUTER JOIN sys.dm_os_tasks t ON (r.session_id = t.session_id AND r.request_id = t.request_id)
LEFT OUTER JOIN
(
-- In some cases (e.g. parallel queries, also waiting for a worker), one thread can be flagged as
-- waiting for several different threads. This will cause that thread to show up in multiple rows
-- in our grid, which we don't want. Use ROW_NUMBER to select the longest wait for each thread,
-- and use it as representative of the other wait relationships this thread is involved in.
SELECT *, ROW_NUMBER() OVER (PARTITION BY waiting_task_address ORDER BY wait_duration_ms DESC) AS row_num
FROM sys.dm_os_waiting_tasks
) w ON (t.task_address = w.waiting_task_address) AND w.row_num = 1
LEFT OUTER JOIN sys.dm_exec_requests r2 ON (s.session_id = r2.blocking_session_id)
--LEFT OUTER JOIN sys.dm_resource_governor_workload_groups g ON (g.group_id = s.group_id)--TAKE THIS dmv OUT TO WORK IN 2005
LEFT OUTER JOIN sys.sysprocesses p ON (s.session_id = p.spid)
ORDER BY s.session_id;
ROLLBACK
August 3, 2011 at 12:34 pm
Thank you for your reply,
I killed the process Yday afternoon around 12 PM but its still showing in the activity monitor.
August 3, 2011 at 12:37 pm
I actually tried creating a linked server to an Oracle dataase.
The SQL is a 64 bit server so I used "Oracle OLD DB Provider " while creating the linked server through management studio.
This was executing and was taking long time so I had to kill the process.
How do I clean this mess without affecting out prod.
Thank you
August 3, 2011 at 12:37 pm
What was it doing before the kill, what is it waiting on now. How long had it been working on before the kill?
August 3, 2011 at 12:38 pm
A restart should do it, but I don't know if there's another way.
What's the current wait info for that spid?
August 3, 2011 at 12:43 pm
The current wait time is 86369625 , the status says KILLED/ROLLBACK
Does this has any affect on the instance level ??
Can I leave it until the reboot schedule ?
August 3, 2011 at 12:50 pm
Assuming nothing is waiting on that spid to do something I think you can wait untill the next server recycle.
August 3, 2011 at 1:00 pm
No nothing is waiting on this spid..also blocked by and blocking is 0.
Thank you again for your reply.
Now, could you help me in the linked server setup to Oracle.
The SQL 2005 is on 64 bit windows 2003, I have installed Oracle 10G 64-bit client on this machine.
While when I was trying to create the linked server the drop-down for the provider doesn't show :
'Microsoft OLE DB Provider for Oracle'.
After reading many msdb I found that Microsoft doesn't have a provider for Oracle for 64 bit, so I have to use Oracle provider which is ' Oracle OLE DB Provider' but using this I was never able to finish the linked server setup and ended up having those killed processes in the activity monitor.
Thank you again,
Sree
August 3, 2011 at 1:03 pm
I never setup linked server to oracle. I'd start a new thread if I were you. You'll get better exposure andbetter chance for a good / quick answer.
August 3, 2011 at 1:05 pm
Thank you again !
August 3, 2011 at 1:53 pm
Check in DTC what the state of the transaction is. Also, try a restart of DTS (not SQL, not the machine, just MSDTC), see if that fixes anything.
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
August 3, 2011 at 3:43 pm
GilaMonster (8/3/2011)
Check in DTC what the state of the transaction is. Also, try a restart of DTS (not SQL, not the machine, just MSDTC), see if that fixes anything.
Gail, I would be very careful with suggesting a restart of MSDTC. I have seen instances where doing so causes SQL Server to shutdown because MSDTC is a required resource for SQL Server.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
August 3, 2011 at 3:52 pm
sree-879715 (8/3/2011)
No nothing is waiting on this spid..also blocked by and blocking is 0.Thank you again for your reply.
Now, could you help me in the linked server setup to Oracle.
The SQL 2005 is on 64 bit windows 2003, I have installed Oracle 10G 64-bit client on this machine.
While when I was trying to create the linked server the drop-down for the provider doesn't show :
'Microsoft OLE DB Provider for Oracle'.
After reading many msdb I found that Microsoft doesn't have a provider for Oracle for 64 bit, so I have to use Oracle provider which is ' Oracle OLE DB Provider' but using this I was never able to finish the linked server setup and ended up having those killed processes in the activity monitor.
Thank you again,
Sree
Just a word of caution - you really should not be performing these kinds of operations on a production system. You need to do this on a test system and document the required steps to build it on production.
You are lucky at this point because those processes are not blocking any resources needed by other processes. However, that may not be the case tomorrow - or next week and you may find yourself in a situation where you have to restart the server to clear these processes.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
August 3, 2011 at 4:09 pm
Thank you all for responding....
Jeff,
Everything worked perfect in the test environment...we didn't have issues in test while setting it up so moved to prod and here we are with prod.
Do you think it will cause any issues if they processes are continued to stay in the activity monitor ?
Is Instance reboot only a way to clear these processes or can I delete/clear them from any system objects ?
any ideas plz
Thank you agian,
Sree
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply