March 26, 2014 at 7:04 am
I set up a couple traces yesterday, 1222 for lock info and 4001 for getting extended login information using -1 so that it's a global setting. I do know that a deadlock occurred since I Implemented the trace. I checked the SQL Server Logs in SQL Server and don't see any information related to the deadlocks, or any information related to bad or successful logins. Am I looking in the wrong place?
March 26, 2014 at 8:45 am
Can you check if the trace flag is still turned on? You can do it with this statement:
DBCC TRACESTATUS(-1)
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
March 26, 2014 at 8:53 am
Status = 1
March 26, 2014 at 10:11 am
Could it be that someone cycled the log since the time that the deadlock happened? If you create deadlock on purpose from SSMS do you see the deadlock's details in the log?
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
March 26, 2014 at 10:15 am
Log is good, it contains no missing data that I can see and goes back several days.
March 26, 2014 at 10:24 am
Do you have the system_health XE session still running?
Also, where did you confirm that a deadlock happened?
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
March 26, 2014 at 10:38 am
We use a 3rd party program that displays this data but it doesn't provide enough for me to do some good research.
I ran select * from sys.dm_xe_sessions and see a return. Am I looking for something specific? It should be defaulted to be on.
March 26, 2014 at 10:47 am
If your system_health session is running, then you can query the ring buffer (the default output of that session) and maybe find the deadlock.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
March 26, 2014 at 10:52 am
SQLRNNR (3/26/2014)
If your system_health session is running, then you can query the ring buffer (the default output of that session) and maybe find the deadlock.
Could you provide more detail as to which DMV I need to use?
March 26, 2014 at 11:08 am
Try this
SELECT xed.value('@timestamp', 'datetime') as Creation_Date,
xed.query('.') AS Extend_Event
FROM ( SELECT CAST([target_data] AS XML) AS Target_Data
FROM sys.dm_xe_session_targets AS xt
INNER JOIN sys.dm_xe_sessions AS xs
ON xs.address = xt.event_session_address
WHERE xs.name = N'system_health'
AND xt.target_name = N'ring_buffer')
AS XML_Data
CROSS APPLY Target_Data.nodes('RingBufferTarget/event[@name="xml_deadlock_report"]') AS XEventData(xed)
ORDER BY Creation_Date DESC
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
March 26, 2014 at 11:59 am
Huh, maybe that really isn't a deadlock which makes me worried about the app. I see a lot of entries here going back a couple weeks, but nothing that correlates to the app notification.
March 26, 2014 at 12:02 pm
Is it possible that the app server has a different timestamp than the database server?
It is possible that the app is misreporting it as a deadlock as well.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
March 26, 2014 at 12:43 pm
Timestamp should be the same. I'm guessing the application is reporting it wrong.
March 26, 2014 at 4:20 pm
You can test it for your self by creating a deadlock and then check that you see it's details in the error log. Here is a small script that creates the deadlock:
use tempdb
go
create table t (i int)
go
create table t1 (i int)
go
insert into t (i) values (1),(2)
insert into t1 (i) values (1),(2)
go
---------Begin creating deadlock
--Run this code on the first window
begin tran
update t set i = i + 1
waitfor delay '00:00:08'
update t1 set i = i + 1
rollback tran
-------run this code on second window
use tempdb
go
begin tran
update t1 set i = i + 1
update t set i = i + 1
rollback
go
/*
--use it to clean drop the table later
drop table t
drop table t1
*/
If you'll run it on 2 separate windows, you should get a deadlock.
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply