Hi, yes, we're looking for a deadlock and not having a lot of success.

What I want to know is how to use trace flag 1204 and related flags to
actually get the deadlocking SQL dumped out to the error log ? We get
output, but it doesn't show us any sql.

Any ideas ?

thanks.
Adrian

Hi

Try the methods described in http://tinyurl.com/39762 you may also want to
look at using profiler to detect this.
The following may also be useful:
http://msdn.microsoft.com/library/de...tabse_5xrn.asp

John

"Adrian Parker" <apparker (AT) nospam (DOT) com> wrote

As I said, we're getting output from trace 1204 et al but it doesn't show
the sql statements that are causing the deadlock, just that the problem is
in cursoropen which is completely useless.

-Adrian

"John Bell" <jbellnewsposts (AT) hotmail (DOT) com> wrote

Hi

Did you look at profiler?

John

"Adrian Parker" <apparker (AT) nospam (DOT) com> wrote

Hi Adrian,

From your descriptions, I understood you would like to know how to analyze
trace flag of 1204 and identifying the deadlock. Have I understood you? If
there is anything I misunderstood, please feel free to let me know

Based on my knowledge, the following documents will show you how to do it

INF: Analyzing and Avoiding Deadlocks in SQL Server
http://support.microsoft.com/?id=169960

INF: Understanding and Resolving SQL Server 7.0 or 2000 Blocking Problems
(224453)
http://support.microsoft.com/?id=224453

INF: How to Monitor SQL Server 2000 Blocking
http://support.microsoft.com/default...B;EN-US;271509

Hope this helps. However, looking at the nature of this issue, it would
require intensive troubleshooting, which might be time-costing and hard to
communication in the Newsgroup. So that I would like to recommand you
request direct assistance from a Microsoft Support Professional through
Microsoft Product Support Services, by which, I believe, would be resolved
quickly and effectively. You can contact Microsoft Product Support directly
to discuss additional support options you may have available, by contacting
us at 1-(800)936-5800 or by choosing one of the options listed at
http://support.microsoft.com/default...d=sz;en-us;top

Thank you for your patience and cooperation. If you have any questions or
concerns, don't hesitate to let me know. We are here to be of assistance!

Sincerely yours,


Mingqing Cheng
Microsoft Developer Community Support
---------------------------------------------------------------
Introduction to Yukon! - http://www.microsoft.com/sql/yukon
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!

After adding debug to the code to pause it at specific intervals, and then
looked in enterprise manager to see the current activity. We found that it
was a lock escalation up to a table lock that was causing the problem..
(pity there isn't a way of telling it not to do that) The profiler wasn't
of much use as there was too much going on to track it down in there.

-Adrian


""Mingqing Cheng [MSFT]"" <v-mingqc (AT) online (DOT) microsoft.com> wrote

Hi Adrian,

From your descriptions, I understood that you find a lock escalation up to
a table lock causing the problem. Have I understood you? If there is
anything I misunderstood, please feel free to let me know.

Based on my knowledge, you should find the SQL statement that lead to the
lock escalation instead of avoiding that escalation.

Anyway, you can disable lock escalation by enabling trace flag 1211.
NOTICE: this trace flag will disable all lock escalation globally in the
instance of SQL Server. Lock escalation serves a very useful purpose in SQL
Server internal management and you may encounter error 1204 after disabling
it.

Detailed information about Lock Escalation in SQL Server could be found at
the following KB:

INF: Resolving Blocking Problems That Are Caused by Lock Escalation in SQL
Server
http://support.microsoft.com/default...b;en-us;323630

Additional Information
==================
Have you upgrade your SQL Server to that latest updates, which could be got
in the following links:

INF: How to Obtain the Latest SQL Server 2000 Service Pack
http://support.microsoft.com/default...b;en-us;290211

Hope this helps and if you have any questions or concerns, please feel free
to let me know

Thank you for your patience and cooperation. If you have any questions or
concerns, don't hesitate to let me know. We are here to be of assistance!


Sincerely yours,

Mingqing Cheng
Microsoft Developer Community Support
---------------------------------------------------------------
Introduction to Yukon! - http://www.microsoft.com/sql/yukon
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!