SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Administration
 Deadlocks
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

MIK_2008
Flowing Fount of Yak Knowledge

Pakistan
1054 Posts

Posted - 03/08/2013 :  08:35:15  Show Profile  Reply with Quote
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

3704 Posts

Posted - 03/08/2013 :  14:26:32  Show Profile  Reply with Quote
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/
Go to Top of Page

jackv
Flowing Fount of Yak Knowledge

United Kingdom
2067 Posts

Posted - 03/08/2013 :  14:42:47  Show Profile  Visit jackv's Homepage  Reply with Quote
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
Go to Top of Page

MIK_2008
Flowing Fount of Yak Knowledge

Pakistan
1054 Posts

Posted - 03/12/2013 :  07:35:10  Show Profile  Reply with Quote
Thanks guys.. I made it event base... but still long way to go .. will do some R&D on it. :)

Cheers
MIK
Go to Top of Page

jackv
Flowing Fount of Yak Knowledge

United Kingdom
2067 Posts

Posted - 03/12/2013 :  12:59:50  Show Profile  Visit jackv's Homepage  Reply with Quote
no problem , let us know how it goes

Jack Vamvas
--------------------
http://www.sqlserver-dba.com
Go to Top of Page

MIK_2008
Flowing Fount of Yak Knowledge

Pakistan
1054 Posts

Posted - 03/12/2013 :  13:40:44  Show Profile  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000