March 5, 2010 at 1:13 am
Before shutting down the SQL Server 2005 instances 3 sessions were suspended.plz let me know reson behind
March 5, 2010 at 2:13 am
Have you checked the Error/Sql log for that interval of time?
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
March 5, 2010 at 4:03 am
Suspended simply means waiting for a resource. So they could have been waiting for a lock, a latch, a memory grant, time on the processor, etc, etc. A look at wait type and wait resource would tell you what they are waiting for.
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
March 5, 2010 at 5:04 am
May be this helps you
select
t1.resource_type,
db_name(resource_database_id)as db_name,
t1.resource_associated_entity_id,
t1.request_mode as lock_requested,
t1.request_session_id as spid_of_waiter ,
t2.wait_duration_ms,
(select text from sys.dm_exec_requests as r --- get sql for waiter
cross apply sys.dm_exec_sql_text(r.sql_handle)
where r.session_id = t1.request_session_id) as get_sql_for_waiter,
(select substring(qt.text,r.statement_start_offset/2,
(case when r.statement_end_offset = -1 then len(convert(nvarchar(max), qt.text)) * 2
else r.statement_end_offset end - r.statement_start_offset)/2)
from sys.dm_exec_requests as r
cross apply sys.dm_exec_sql_text(r.sql_handle) as qt
where r.session_id = t1.request_session_id) as statement_executing_now, --- this is the statement executing right now
t2.blocking_session_id ,-- spid of blocker
(select text from sys.sysprocesses as p--- get sql for blocker
cross apply sys.dm_exec_sql_text(p.sql_handle) where p.spid = t2.blocking_session_id) as get_sql_for_blocker,
getdate() as getdate
from sys.dm_tran_locks as t1,
sys.dm_os_waiting_tasks as t2
where t1.lock_owner_address = t2.resource_address
-------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