Deadlocks - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Administration

Deadlocks

Author

Topic

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2013-03-08 : 08:35:15

Hello team, I want to save the deadlocks events that are generated through out the day for a particular sql instance/database in a table? Do I need a trigger for this purpose? if so on which table? or any other sulution?
Thanks!

Cheers
MIK

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-03-08 : 14:26:32

See if this article and Paul Randal's article that is referred to in that article will help you: http://www.sqlservercentral.com/articles/deadlock/65658/

jackv
Master Smack Fu Yak Hacker

2179 Posts

Posted - 2013-03-08 : 14:42:47

This post outlines how to capture and troubleshoot blocking and deadlocks - http://www.sqlserver-dba.com/2012/11/sql-server-how-to-detect-and-troubleshoot-blocking-and-deadlocks.html

Jack Vamvas
--------------------
http://www.sqlserver-dba.com

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2013-03-12 : 07:35:10

Thanks guys.. I made it event base... but still long way to go .. will do some R&D on it. :)

Cheers
MIK

jackv
Master Smack Fu Yak Hacker

2179 Posts

Posted - 2013-03-12 : 12:59:50

no problem , let us know how it goes

Jack Vamvas
--------------------
http://www.sqlserver-dba.com

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2013-03-12 : 13:40:44

Back with an issue. I've created an event

CREATE EVENT SESSION CaptureDeadlocks_On_test_Database
ON SERVER
ADD EVENT sqlserver.lock_deadlock
(ACTION
(sqlserver.sql_text
,sqlserver.database_id
,sqlserver.client_app_name)
WHERE sqlserver.database_id = 10)
ADD TARGET package0.asynchronous_file_target
(SET filename = N'E:\CaptureDeadlocks_On_test_Database.xet'
,metadatafile = N'E:\CaptureDeadlocks_On_test_Database.xem'
,max_file_size = 10
,max_rollover_files = 10);

and have started it.
I went through a dead lock situation

-------------------------
--session1
update table1 set col1=10 where col1=1
--session2
update table2 set col1='a' where col1='z'

--session1
update table2 set col1='a' where col1='z'
--session2
update table1 set col1=10 where col1=1

deadlock occured. SQL Server terminated one of the process.

But now, when I try to read the data from .xet and .xem files using [sys].[fn_xe_file_target_read_file], it is not showing any record. whereas RingBuffer has this information (dm_xe_session_targets)? Any help what I am doing wrong? thanks in Advance!

Cheers
MIK

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources