I'm told by the software vendor that an application failing to record may be due to a SQL deadlock. They suggested I monitor the database for deadlocks.
Is there a monitoring process or procedure that will do this?
Sam
You have couple of different ways to capture the info. See here
I haven't tried the profiler or the extended events that are described there. I have always used the trace.
Trace you say? Tell me more...
Pretty much what it says in that article on Brent Ozar's site
Use trace flags to write to the log
Two trace flags can be enabled to capture more information in the log: 1204 and 1222. 1204 lists the information by node; 1222 lists it by process and resource. You can enable both simultaneously. To enable the flags, use the command
DBCC TRACEON (1204, 1222)
Then you leave it alone and wait for a deadlock to happen. When that happens if you open up the log it will have pretty detailed information about the processes that deadlocked. There may be more than two processes involved, and it may be hard/painful to read and decode, but the information is in there.
Hi SamC,
Blame it to the DB, that what normally those software vendor do. If you think about how a deadlock situation occurs, is 2 processes each holding something the others wants (update), and that is on a given row/table (normally). Unless you have other 3rd party product that doing things on the same DB, guess what, its their own code that mess up!
I saw many of those cases where vendor's code are messy and most of the times they have different person writing different part of the code, which leads to different method of updating the same table(s), and it leads to deadlock. It might not be the same process (as you might not see that happen every single time), so might worth to try to capture it and send the info to the vendor and ask them to fix it from their code.
1 Like
I implemented an error log using some code Graz provided and found no errors at all whic presumably means no deadlocks.
Sad to say the ASP side code has no error logging. My assumption is an error is pulled somewhere running ASP which stops the script.
Sam