0

Is there a way to get information about other events related the the process, that where not caught by SQL-server profiler (not included in filter list).

For instance, if I only trace the deadlock-graphs and when I catch that kind of event, can I somehow get info about, say, the BatchStarted or BatchCompleted event that happened before/after the caught event inside the same process?

I'm in a situation when I want to know to values of parameters used in the query, but the deadlock-graph event does not provide such info. I can re-configure the profiler to trace other events but I don't know how to reproduce the deadlock.

Artur Udod
  • 4,465
  • 1
  • 29
  • 58

1 Answers1

1

You cannot get more info that the one you originally traced. But maybe you still don't know that you can get extra event data from your deadlock-graph by right clicking on it and saving the Extra Event Data into a XML file (with XDL extension -Deadlock XML files-, but I suggest to use a XML editor to read it) with a callstack for every process. It may add the extra piece of information that you need to solve your deadlock. Good Luck.

It is perfectly explained here:

http://beyondrelational.com/modules/2/blogs/77/posts/11368/sql-server-ssms-profiler-extracting-deadlock-event-data-to-xdl-files.aspx

Jaime
  • 1,110
  • 1
  • 8
  • 14
  • Well, off course =) actually i've configured the profiler to create xdl files for each deadlock automatically. I have no problem with reading that xml info and identifying the reason of deadlock. But i just can't figure out the exact place in our system where this happens =) and deadlock-graph event does not provide info about the values of parameters passed to the batch. Anyway, thank you ) – Artur Udod Oct 03 '12 at 10:13
  • @ArturUdod doesn't the stack in the XML file help you to better localize the place where it happens? – Jaime Oct 03 '12 at 10:55
  • Well, what kind of info needed for that does it provide? sql-statements, information about loginname, time, process id, etc + off course information about lock and transactions. I have many places in my system which perform similar requests – Artur Udod Oct 03 '12 at 12:05
  • @ArturUdod This might be helpful for you to try to catch the deadlocks: SQL Server Extended Events - http://msdn.microsoft.com/en-us/library/bb630354(v=sql.105).aspx – Jaime Oct 04 '12 at 07:13