February 20, 2007 at 4:32 am
I suspect the answer is "no" but I thought I'd ask all the same!
There was a batch process running through query analyser on SQL 2000 enterprise. It was the chosen as the deadlock victim. The mystery I have is that as far as I am aware, there were no other processes running at the time, so I can't understand how this deadlock occured. Is it possible to find out after the event what caused this? This is a process that is run daily and runs for several hours and has not been changed recently. I don't think it has been deadlocked before.
As I wasn't expecting this to happen, the relevant trace flags were not switched on, and there is nothing in the log. I'd like to get to the bottom of this in case it happens again.
Any ideas greatly appreciated.
February 20, 2007 at 4:54 am
Its too late the event has passed. You could add an alert to tell you of a deadlock, which you possibly should anyway, this will allow you to get the count of how many times it is happening or whether this was a one off.
You can use the following to generate a script to look at what is happening at any point in time, whilst it is running.
USE master
GO
DECLARE @spidno varchar(10),
@str varchar(100)
DECLARE newspidCur CURSOR FOR
SELECT spid FROM sysprocesses
-- If you want to look for specific users or applications amend as appropiate ie WHERE program_name like '.net%'
OPEN newspidCur
FETCH NEXT FROM newspidCur INTO @spidno
WHILE @@fetch_status = 0
BEGIN
SET @STR = 'dbcc inputbuffer('+ @spidno +')'
PRINT @STR
FETCH NEXT FROM newspidCur INTO @spidno
END
CLOSE newspidCur
DEALLOCATE newspidCur
I find this useful to look are what's happening when the server is running slowly, unless you are watching , profiling or tracing it is difficult to capture what caused the deadlock.
February 20, 2007 at 5:11 am
Thanks for your help Carolyn!
February 20, 2007 at 5:38 am
If deadlocks are a problem then enabling trace flags will store all the data you need in the sql log, but you do get large logs so need to use with care.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
February 20, 2007 at 9:11 am
The only way to troubleshoot deadlock will be to see the deadlock graph, to get those you need to enable the traceflags (1205 and 3604, i believe). This will dump the deadlock graph to your sql errorlogs. so if you are not getting hundreds of deadlock in a minute , you will have to enable these falgs to furhter troubleshoot .
I dont believe the logs would be that large in size
February 20, 2007 at 12:54 pm
you may need 1204 too - I figure about 80mb a day ( av ) with trace falgs enabled.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
February 26, 2007 at 6:57 am
The logs shouldn't be a problem, we get less than 1 deadlock per week. We've switched on the trace flags and are now waiting for a deadlock to occur.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply