| Author |
 Topic  |
|
|
MIK_2008
Aged Yak Warrior
Pakistan
827 Posts |
 Posted - 03/08/2013 : 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
Flowing Fount of Yak Knowledge
1527 Posts |
|
|
jackv
Flowing Fount of Yak Knowledge
United Kingdom
1773 Posts |
|
|
MIK_2008
Aged Yak Warrior
Pakistan
827 Posts |
Posted - 03/12/2013 : 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
Flowing Fount of Yak Knowledge
United Kingdom
1773 Posts |
|
|
MIK_2008
Aged Yak Warrior
Pakistan
827 Posts |
Posted - 03/12/2013 : 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 |
|
|
| |
Topic |
|
Deadlocks
