August 10, 2011 at 10:42 am
Hi,
Blocking on my production server
select * from sysprocesses where blocked<>0
sp id blcoking '544'
24162085440x0004102375LCK_M_U KEY: 9:72057594051690496 (68001e36f7e1)
25947965440x0004116046LCK_M_U KEY: 9:72057594051690496 (6400a68921ab)
45262605440x0004112812LCK_M_U KEY: 9:72057594051690496 (6a0095fefe4b)
45463685670x0004980765LCK_M_U KEY: 9:72057594054115328 (3400fea190fd)
67465045440x000496375LCK_M_U KEY: 9:72057594051690496 (6c0049a1956e)
68558485440x0004116046LCK_M_U KEY: 9:72057594051690496 (6e00c2699cc4)
dbcc inputbuffer(544)
How to reslove this ? in this session -544 UPDATE operation is running I need wait or KILL the session ???
Rajesh
August 10, 2011 at 10:49 am
Do you know what it's doing? Do you know what effects killing it will have on the business applications?
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 10, 2011 at 10:50 am
Update is going on this session, I'm new to sql dba
5-6 blocking sessions are they that’s why i ask to you ? Can i kill
Plz give me explanation
August 10, 2011 at 10:58 am
Do you know what those operations are doing? Do you know what effects killing those connections will have on the applications that use the database?
If you don't and you kill the connections, you're taking a risk and hoping that it's not going to mess things up (maybe that's your salary being processed there, you don't know)
If you don't know, find out. See what the blocking session is running, see where the connection was made from, who's running it. Ask someone who knows if it's safe to cancel whatever that is.
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 10, 2011 at 11:11 am
Thanks Gila
August 10, 2011 at 11:30 am
rajeshn29.dba (8/10/2011)
Thanks Gila
This can help you have a much better understanding of what's going on (doesn't replace asking around, but helps a lot).
This is what activity monitor runs. You can uncomment an extra join if you run this on 2k8.
--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;
August 10, 2011 at 11:45 am
Thanks alot
Gila & Ninja
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply