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.
| 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 |
|
|
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 |
|
|
|
|
|
|
Deadlocks