November 11, 2010 at 12:27 am
Hi all,
I am facing an issue where my update statement is blocked by below statement(Inactive):
"use dbname"
is any body having an idea why? and whats the workaround to avoid.
thanks
Puneet
November 11, 2010 at 1:14 am
pujain (11/11/2010)
Hi all,I am facing an issue where my update statement is blocked by below statement(Inactive):
"use dbname"
is any body having an idea why? and whats the workaround to avoid.
thanks
Puneet
you didn't post near enough information for any assistance....
what does you update statement look like etc???
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
This thing is addressing problems that dont exist. Its solution-ism at its worst. We are dumbing down machines that are inherently superior. - Gilfoyle
November 11, 2010 at 1:27 am
pujain (11/11/2010)
Hi all,I am facing an issue where my update statement is blocked by below statement(Inactive):
"use dbname"
is any body having an idea why? and whats the workaround to avoid.
thanks
Puneet
i am not sure, this could be the cause.
Use DBCC opentran , Sp_lock, Sp_who2 and then use the dbcc inputbuffer("culprit spid")
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
November 11, 2010 at 1:30 am
Use below queries for troubleshoot.these would be more informative.
--use ESS_ARCHIVE
--select * from sys.dm_os_wait_stats
select r.cpu_time
, r.logical_reads
, r.session_id
into #temp
from sys.dm_exec_sessions as s
inner join sys.dm_exec_requests as r
on s.session_id =r.session_id --and s.last_request_start_time=r.start_time
where is_user_process = 1
--waitfor delay '00:00:01'
select substring(h.text, (r.statement_start_offset/2)+1 , ((case r.statement_end_offset when -1
then datalength(h.text) else r.statement_end_offset end - r.statement_start_offset)/2) + 1) as text
, r.cpu_time-t.cpu_time as CPUDiff
, r.logical_reads-t.logical_reads as ReadDiff
, p.query_plan
, r.wait_type
, r.wait_time
, r.last_wait_type
, r.wait_resource
, r.command
, r.database_id
, r.blocking_session_id
, r.granted_query_memory
, r.session_id
, r.reads
, r.writes
, r.row_count
, s.[host_name]
, s.program_name
, s.login_name
from sys.dm_exec_sessions as s
inner join sys.dm_exec_requests as r
on s.session_id =r.session_id and s.last_request_start_time=r.start_time
full outer join #temp as t on t.session_id=s.session_id
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) h
cross apply sys.dm_exec_query_plan(r.plan_handle) p
order by 3 desc
drop table #temp
go
exec sp_who2 'active'
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply