October 26, 2010 at 6:57 am
lavien (10/26/2010)
hix.., i can not know why.
Well you need to investigate and find out. Can't just sit and hope the problem will go away
Check the blocking, that would be my first guess, check the wait type as well.
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
October 27, 2010 at 8:42 am
lavien (10/26/2010)
I don't know why it has to rollback. The server have more than 30 process that the select statement using the full text catalog for some days(I had been DBA of that server in a few days). I killed them all but it's change to Killed/Rollback status.Now i try to duplicate each objects in that db to another for backup. do you have some suggestion?
thanks.
You can try using the following queries to get more detail about what exactly these zombie processes are/were doing and what they're waiting for.
-- Listing currently running or pending processes:
SELECT
r.session_id,
r.start_time,
r.status AS requestStatus
,r.command AS requestCommandType
,OBJECT_NAME(qt.objectid) AS callingObject
,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) AS IndividualQueryText
,USER_NAME(r.[user_id]) AS userName,
r.blocking_session_id,
r.wait_time,
r.cpu_time,
r.total_elapsed_time,
r.reads,
r.logical_reads,
r.writes,
r.row_count
FROM sys.dm_exec_requests AS r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS qt
where
r.session_id <> @@SPID;
select
t1.resource_type,
'database'=db_name(resource_database_id),
'blk object' = t1.resource_associated_entity_id,
t1.request_mode,
t1.request_session_id,
t2.blocking_session_id
from
sys.dm_tran_locks as t1,
sys.dm_os_waiting_tasks as t2
where
t1.lock_owner_address = t2.resource_address
-- The following query shows just those waits that have been occurring for more than 5 seconds:
SELECT
WT.session_id AS waiting_session_id,
WT.waiting_task_address,
WT.wait_duration_ms,
WT.wait_type,
WT.blocking_session_id,
WT.resource_description
FROM sys.dm_os_waiting_tasks AS WT
WHERE WT.wait_duration_ms > 5000;
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
Viewing 2 posts - 16 through 17 (of 17 total)
You must be logged in to reply to this topic. Login to reply