April 8, 2013 at 10:39 pm
Hi Guys,
I have two questions.
1) My first question, how i can find out on which table of my database has dead lock problem?
2) If i restart my server all dead lock problem solve mean go away or not? or still i have to kill each process manually?
Thank You.
April 8, 2013 at 11:11 pm
rocky_498 (4/8/2013)
Hi Guys,I have two questions.
1) My first question, how i can find out on which table of my database has dead lock problem?
2) If i restart my server all dead lock problem solve mean go away or not? or still i have to kill each process manually?
Thank You.
1) Quick method - select * from master..sysprocesses
Look into column waitresource - should give you an idea
You can also use Profiler to catch deadlock events.
2. No, waste of time. "Or" - yes. This or another way you have to resolve deadlock issue.
_____________
Code for TallyGenerator
April 9, 2013 at 1:07 am
First of all:
Get some understanding of what a deadlock is !
- http://msdn.microsoft.com/en-us/library/ms178104%28v=sql.105%29.aspx
tech art. on "How to resolve a deadlock"
- http://support.microsoft.com/kb/832524
The above articles may get you started with the topic.
Address the issues, in stead of rebooting your instances !
Set QUOTED_IDENTIFIER ON;
go
/* Fetch the Health Session data into a temporary table (SQL2008+)*/
if object_id('tempdb..#SystemHealthSessionData') is null
begin
/* ALZDBA: add XMLIndex for faster consumption ! */
Create table #SystemHealthSessionData
( PK int not null primary key clustered,
XMLDATA xml not null
);
CREATE PRIMARY XML INDEX PXML_SystemHealthSessionData
ON #SystemHealthSessionData (XMLDATA);
insert INTO #SystemHealthSessionData
SELECT cast( 1 as int ) as PK, CAST(xet.target_data AS XML) AS XMLDATA
FROM sys.dm_xe_session_targets xet
INNER JOIN sys.dm_xe_sessions xe
ON ( xe.address = xet.event_session_address )
WHERE xe.name = 'system_health';
end ;
;
with cteDeadLocks
as (
SELECT top 250
x.y.value('(@timestamp)[1]', 'datetime') [timestampUTC]
, CAST(x.y.value('data(.//data/value)[1]', 'VARCHAR(MAX)') AS XML).value('deadlock[1]/victim-list[1]/victimProcess[1]/@id[1]', 'VARCHAR(128)') [victim]
, CAST(x.y.value('data(.//data/value)[1]', 'VARCHAR(MAX)') AS XML).query('deadlock/process-list/*') [process-list]
, CAST(x.y.value('data(.//data/value)[1]', 'VARCHAR(MAX)') AS XML).query('deadlock/resource-list/*') [resource-list]
, CAST(x.y.value('data(.)[1]', 'VARCHAR(MAX)') AS XML).query('*') as DeadlockGraph
FROM #SystemHealthSessionData [deadlock]
CROSS APPLY [XMLDATA].nodes('/RingBufferTarget/event') AS x ( y )
WHERE x.y.query('.').exist('/event[@name="xml_deadlock_report"]') = 1
and x.y.value('(@timestamp)[1]', 'datetime') > DATEADD(dd, datediff(dd, 0, getUTCdate()) - 2 , 0)
Order by [timestampUTC] desc
)
Select convert(varchar(25),SERVERPROPERTY('ServerName')) as ServerName
, *
/* process block 1 */
, [resource-list].value('(//@dbid)[1]', 'integer') [DbId1]
, DB_NAME( [resource-list].value('(//@dbid)[1]', 'integer')) [DbName1]
, [process-list].value('(//@id)[1]', 'nvarchar(128)') [Id1]
, [process-list].value('(//@status)[1]', 'nvarchar(128)') [Status1]
, [process-list].value('(//@waitresource)[1]', 'nvarchar(128)') [waitresource1]
, [process-list].value('(//@waittime)[1]', 'integer') [waittime1]
, [process-list].value('(//@transactionname)[1]', 'nvarchar(128)') [transactionname1]
, [process-list].value('(//@clientapp)[1]', 'nvarchar(256)') [ApplicationName1]
, [process-list].value('(//@hostname)[1]', 'nvarchar(256)') [hostname1]
, [process-list].value('(//@loginname)[1]', 'nvarchar(256)') [loginname1]
, [process-list].value('(//@isolationlevel)[1]', 'nvarchar(256)') [isolationlevel1]
, [process-list].value('(//@currentdb)[1]', 'integer') [ConnectionCurrentDb1]
, DB_NAME( [process-list].value('(//@currentdb)[1]', 'integer')) [ConnectionCurrentDbName1]
, [process-list].value('(//@lasttranstarted)[1]', 'datetime') [lasttranstarted1]
, [process-list].value('(//@lastbatchstarted)[1]', 'datetime') [lastbatchstarted1]
/* process block 2 */
, [resource-list].value('(//@dbid)[2]', 'integer') [DbId2]
, DB_NAME( [resource-list].value('(//@dbid)[2]', 'integer')) [DbName2]
, [process-list].value('(//@id)[2]', 'nvarchar(128)') [Id2]
, [process-list].value('(//@status)[2]', 'nvarchar(128)') [Status2]
, [process-list].value('(//@waitresource)[2]', 'nvarchar(128)') [waitresource2]
, [process-list].value('(//@waittime)[2]', 'integer') [waittime2]
, [process-list].value('(//@transactionname)[2]', 'nvarchar(128)') [transactionname2]
, [process-list].value('(//@clientapp)[2]', 'nvarchar(256)') [ApplicationName2]
, [process-list].value('(//@hostname)[2]', 'nvarchar(256)') [hostname2]
, [process-list].value('(//@loginname)[2]', 'nvarchar(256)') [loginname2]
, [process-list].value('(//@isolationlevel)[2]', 'nvarchar(256)') [isolationlevel2]
, [process-list].value('(//@currentdb)[2]', 'integer') [ConnectionCurrentDb2]
, DB_NAME( [process-list].value('(//@currentdb)[2]', 'integer')) [ConnectionCurrentDbName2]
, [process-list].value('(//@lasttranstarted)[2]', 'datetime') [lasttranstarted2]
, [process-list].value('(//@lastbatchstarted)[2]', 'datetime') [lastbatchstarted2]
/* process block 3 */
, [resource-list].value('(//@dbid)[3]', 'integer') [DbId3]
, DB_NAME( [resource-list].value('(//@dbid)[3]', 'integer')) [DbName3]
, [process-list].value('(//@id)[3]', 'nvarchar(128)') [Id3]
, [process-list].value('(//@status)[3]', 'nvarchar(128)') [Status3]
, [process-list].value('(//@waitresource)[3]', 'nvarchar(128)') [waitresource3]
, [process-list].value('(//@waittime)[3]', 'integer') [waittime3]
, [process-list].value('(//@transactionname)[3]', 'nvarchar(128)') [transactionname3]
, [process-list].value('(//@clientapp)[3]', 'nvarchar(256)') [ApplicationName3]
, [process-list].value('(//@hostname)[3]', 'nvarchar(256)') [hostname3]
, [process-list].value('(//@loginname)[3]', 'nvarchar(256)') [loginname3]
, [process-list].value('(//@isolationlevel)[3]', 'nvarchar(256)') [isolationlevel3]
, [process-list].value('(//@currentdb)[3]', 'integer') [ConnectionCurrentDb3]
, DB_NAME( [process-list].value('(//@currentdb)[3]', 'integer')) [ConnectionCurrentDbName3]
, [process-list].value('(//@lasttranstarted)[3]', 'datetime') [lasttranstarted3]
, [process-list].value('(//@lastbatchstarted)[3]', 'datetime') [lastbatchstarted3]
/* process block 4 */
, [resource-list].value('(//@dbid)[4]', 'integer') [DbId4]
, DB_NAME( [resource-list].value('(//@dbid)[4]', 'integer')) [DbName4]
, [process-list].value('(//@id)[4]', 'nvarchar(128)') [Id4]
, [process-list].value('(//@status)[4]', 'nvarchar(128)') [Status4]
, [process-list].value('(//@waitresource)[4]', 'nvarchar(128)') [waitresource4]
, [process-list].value('(//@waittime)[4]', 'integer') [waittime4]
, [process-list].value('(//@transactionname)[4]', 'nvarchar(128)') [transactionname4]
, [process-list].value('(//@clientapp)[4]', 'nvarchar(256)') [ApplicationName4]
, [process-list].value('(//@hostname)[4]', 'nvarchar(256)') [hostname4]
, [process-list].value('(//@loginname)[4]', 'nvarchar(256)') [loginname4]
, [process-list].value('(//@isolationlevel)[4]', 'nvarchar(256)') [isolationlevel4]
, [process-list].value('(//@currentdb)[4]', 'integer') [ConnectionCurrentDb4]
, DB_NAME( [process-list].value('(//@currentdb)[4]', 'integer')) [ConnectionCurrentDbName4]
, [process-list].value('(//@lasttranstarted)[4]', 'datetime') [lasttranstarted4]
, [process-list].value('(//@lastbatchstarted)[4]', 'datetime') [lastbatchstarted4]
from cteDeadLocks
/*
where Database_Id = DB_ID()
*/
order by [timestampUTC] desc ;
-- DROP TABLE #SystemHealthSessionData
p.s. never mind the lousy xqueries :unsure:
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
April 9, 2013 at 3:01 pm
Turn on trace flag 1222:
DBCC TRACEON ( 1222, -1 )
Then when a deadlock occurs, SQL will put detailed info about the deadlock into the SQL error log.
Rebooting will not help at all with deadlocking.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply