August 21, 2013 at 3:22 am
I am asked to check whether their was any deadlock occured 15 days back.. how do i do this :w00t:
************************************
Every Dog has a Tail !!!!! :-D
August 21, 2013 at 3:33 am
If you don't have any monitoring system set up that captures those events, then you can't do it. It may be worth looking in the default trace, but I'm not sure whether it includes deadlocks... and in any case, it may not hold information going back 15 days.
John
Edit - have you tried looking in application logs? If the application doesn't handle deadlocks gracefully then it may pass the error message straight through to the log.
August 21, 2013 at 3:38 am
Someone told me to check in error log..
but how i would read & query in error log to find deadlock victim which had happened 15 days back...
************************************
Every Dog has a Tail !!!!! :-D
August 21, 2013 at 3:50 am
August 21, 2013 at 3:51 am
Expand the "Management" and "SQL Server Logs" tree in SSMS. You will see all logs available. The date/time value indicates the moment of the most recent entry in that log.
If the oldest log doesn't contain logging of 15 days ago, it's not possible to find out if a deadlock occured.
And as the John Mitchell allready indicates: without setting a trace flag or something else, it's not possible to find detailed information about a deadlock.
August 21, 2013 at 3:54 am
In error logs, i saw a file name ending with
current - 6/23/2013 10:00:00 AM
& i am asked to provide deadlock victim info of 9th Aug 2013...
More over i got a script to view data
CREATE PROC [sp_readerrorlog](
@p1 INT = 0,
@p2 INT = NULL,
@p3 VARCHAR(255) = NULL,
@p4 VARCHAR(255) = NULL)
AS
BEGIN
IF (NOT IS_SRVROLEMEMBER(N'securityadmin') = 1)
BEGIN
RAISERROR(15003,-1,-1, N'securityadmin')
RETURN (1)
END
IF (@p2 IS NULL)
EXEC xp_readerrorlog @p1
ELSE
EXEC xp_readerrorlog @p1,@p2,@p3,@p4
END
--- EXEC xp_readerrorlog
Please tell me will this work or suggest something
************************************
Every Dog has a Tail !!!!! :-D
August 21, 2013 at 4:01 am
When i executed above SP, i got many records from which their were only 10 records for that date (9th August2013)
The records were only Backup one.. stating info about Log was backed up & database backed up thats it..
Does this mean their was nothing happened on that day except backup task???
************************************
Every Dog has a Tail !!!!! :-D
August 21, 2013 at 4:14 am
OnlyOneRJ (8/21/2013)
Does this mean their was nothing happened on that day except backup task???
No, it does mean: nothing that need to be logged happened on that day
August 21, 2013 at 4:20 am
Hanshi, i did not understand you wrote 🙁
************************************
Every Dog has a Tail !!!!! :-D
August 21, 2013 at 4:23 am
OnlyOneRJ (8/21/2013)
Does this mean their was nothing happened on that day except backup task???
No, it doesn't. It means that backup tasks were the only activity that was written to the log that day. There are several ways of configuring what information gets logged. Have you read the article I posted the link to?
John
August 21, 2013 at 4:29 am
Yes John,
I went through the link.. that will help me in future processings..
But i need to know for 15 days back info... 🙁
One question -
The Link you gave me.. if i do activate it & folow the steps.. will i be able to track the info daily as i need?
Will that impact the performance or any other issue..
************************************
Every Dog has a Tail !!!!! :-D
August 21, 2013 at 4:34 am
The url states
to activate DBCC trace
=====
DBCC TRACEON (1222, -1)
=====
Well how do we deactivate it? as this cant be kept on for long period i think
************************************
Every Dog has a Tail !!!!! :-D
August 21, 2013 at 4:40 am
OnlyOneRJ (8/21/2013)
Well how do we deactivate it? as this cant be kept on for long period i think
Read Books OnLine and/or google a bit on the internet. You will find the command "DBCC TRACEOFF ( trace# [ ,...n ] [ , -1 ] ) [ WITH NO_INFOMSGS ]" to turn off the traceflag.
August 21, 2013 at 5:46 am
Presuming you're on SQL 2008, you could try querying the Extended Events. Depending on how much has gone on since the day you need, however, the information may already have fallen out.
Use this query to get a listing of deadlock events with an *APPROXIMATE* date:
SELECT event.c.value('@timestamp', 'datetime2(3)'),
CAST(datavalue.c.value('(./text())[1]', 'nvarchar(max)') as xml)AS DeadlockGraph
FROM
(
SELECT CAST(target_data AS XML) AS TargetData
FROM sys.dm_xe_session_targets st
JOIN sys.dm_xe_sessions s ON s.address = st.event_session_address
WHERE name = 'system_health'
) AS DATA
CROSS APPLY TargetData.nodes('/RingBufferTarget/event') AS event(c)
CROSS APPLY event.c.nodes ('data/value') AS datavalue(c)
WHERE event.c.value('@name', 'varchar(4000)') = 'xml_deadlock_report'
It will return the XML Deadlock information. You *MAY* be able to save the XML to a file and get the deadlock graphic, but this hasn't worked for me. Otherwise, you can use this query:
declare @deadlock xml
set @deadlock = 'put your deadlock graph here'
select
[PagelockObject] = @deadlock.value('/deadlock[1]/resource-list[1]/pagelock[1]/@objectname', 'varchar(200)'),
[DeadlockObject] = @deadlock.value('/deadlock[1]/resource-list[1]/objectlock[1]/@objectname', 'varchar(200)'),
[KeyLockObject] = @deadlock.value('/deadlock[1]/resource-list[1]/keylock[1]/@objectname', 'varchar(200)'),
[KeyLockIndex] = @deadlock.value('/deadlock[1]/resource-list[1]/keylock[1]/@indexname', 'varchar(200)'),
[Victim] = case when Deadlock.Process.value('@id', 'varchar(50)') = @deadlock.value('/deadlock[1]/victim-list[1]/victimProcess[1]/@id', 'varchar(50)') then 1 else 0 end,
[ProcessID] = Deadlock.Process.value('@id', 'varchar(50)'),
[Procedure] = Deadlock.Process.value('executionStack[1]/frame[1]/@procname[1]', 'varchar(200)'),
[LockMode] = Deadlock.Process.value('@lockMode', 'char(5)'),
[Code] = Deadlock.Process.value('executionStack[1]/frame[1]', 'varchar(1000)'),
--[ClientApp] = Deadlock.Process.value('@clientapp', 'varchar(100)'),
[HostName] = Deadlock.Process.value('@hostname', 'varchar(20)'),
[LoginName] = Deadlock.Process.value('@loginname', 'varchar(20)'),
[TransactionTime] = Deadlock.Process.value('@lasttranstarted', 'datetime'),
[BatchTime] = Deadlock.Process.value('@lastbatchstarted', 'datetime'),
[InputBuffer] = Deadlock.Process.value('inputbuf[1]', 'varchar(1000)'),
[SQLHandle] = Deadlock.Process.value('executionStack[1]/frame[1]/@sqlhandle[1]', 'varchar(200)')
from @deadlock.nodes('/deadlock/process-list/process') as Deadlock(Process)
which was created by WayneS. Copy the entire XML from the first query and put it in place of the 'put your deadlock graph here' (keep the single quotes) and run it. It will give you an easier to read idea as to what happened.
Then, research, research, research.
August 21, 2013 at 6:03 am
I need it for Both sql 2005 & 2008
For 2005 - What should i do???
For 2008 -
Well should i paste the output of first query in to the next query?? where 'Put your graph here' is written???
************************************
Every Dog has a Tail !!!!! :-D
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply