On Sep 02 2007, 01:39 pm, Erland Sommarskog <esquel@sommarskog.se> wrote
in news:Xns999FC8952760AYazorman@127.0.0.1:

> Anyway, you can easily examine this next time it happens by running
>
> SELECT * FROM sys.dm_os_tasks WHERE session_id = <trouble spid>
>
> If there are rows with non-zero exec_context_id, there are parallel
> threads.

There are not, so I guess we can rule out parallelism.

> It's also available in sys.dm_exec_text_query_plan. A way to get the
> plan, sys.os_waiting_tasks and more packaged into one result set, is
> to use my beta_lockinfo, available at
> http://www.sommarskog.se/sqlutil/beta_lockinfo.html

This should be handy. Thanks.

> There have been some bugs around temp-table caching, I don't if they
> could be related to what you see. There is a Cumultative Update,
> including these two bugfixes at
> http://support.microsoft.com/kb/939537.

I'll get that and watch how it goes for a few days. If it still happens,
will try to find some time to work on a repro. Will follow-up with any
news.

--
remove a 9 to reply by email

On Sep 02 2007, 11:44 pm, Dimitri Furman <dfurman@cloud99.net> wrote in
news:Xns999FF19D6AD96dfurmancloud99@127.0.0.1:

> If it still happens,
> will try to find some time to work on a repro. Will follow-up with any
> news.

Submitted feedback on Connect that includes a repro:
https://connect.microsoft.com/SQLSer...Feedback.aspx?
FeedbackID=300465

--
remove a 9 to reply by email

Dimitri Furman (dfurman@cloud99.net) writes:
> On Sep 02 2007, 11:44 pm, Dimitri Furman <dfurman@cloud99.net> wrote in
> news:Xns999FF19D6AD96dfurmancloud99@127.0.0.1:
>
>> If it still happens,
>> will try to find some time to work on a repro. Will follow-up with any
>> news.
>
> Submitted feedback on Connect that includes a repro:
> https://connect.microsoft.com/SQLSer...Feedback.aspx?
> FeedbackID=300465

Thanks Dimitri. Looks like an excellent bug report. I hope that it will
be sufficient for the SQL Server people to track down the bug.

Unfortunately, it is not possible to access attachments on Connect, so
I cannot try the repro. I tried to compose my own from your description,
but it was not really that simple. Given the trouble you had in recreating
it, I wasn't suprised.

If it's possible for you to post the repro files here, I'd be interested.



--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

On Sep 27 2007, 06:01 pm, Erland Sommarskog <esquel@sommarskog.se> wrote in
news:Xns99B9FF37989Yazorman@127.0.0.1:

> If it's possible for you to post the repro files here, I'd be interested.

Here it is:
http://iridule.net/cu/files/SS2005LockingBugRepro1.zip

Thanks for ing me nail it down.

--
remove a 9 to reply by email

Dimitri Furman (dfurman@cloud99.net) writes:
> On Sep 27 2007, 06:01 pm, Erland Sommarskog <esquel@sommarskog.se> wrote
> in news:Xns99B9FF37989Yazorman@127.0.0.1:
>> If it's possible for you to post the repro files here, I'd be interested.
>
> Here it is:
> http://iridule.net/cu/files/SS2005LockingBugRepro1.zip

Got it, and indeed I had to reboot myserver. What was missing from your
description on Connect was the RECOMPILE hint. When I remove it, the
procedure completes.

I looked in the SQL Server error log, and I found that there is a
stack dump for an unresolved deadlock.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

By the way, I tried the repro on the July CTP of SQL 2008, and the error
does not occur there, but the procedure completes successfully.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

On Sep 28 2007, 05:28 pm, Erland Sommarskog <esquel@sommarskog.se> wrote
in news:Xns99B9EFAA31154Yazorman@127.0.0.1:

> Got it, and indeed I had to reboot myserver. What was missing from
> your description on Connect was the RECOMPILE hint. When I remove it,
> the procedure completes.

The RECOMPILE hint is there to make it consistently reproducible. Before I
added that, the issue only occurred intermittently (apparently, when the
function's plan had to be recompiled).

> I looked in the SQL Server error log, and I found that there is a
> stack dump for an unresolved deadlock.

I wish I had seen that in the beginning, at least I could be certain it's a
real problem.

I updated the bug report to include these two bits of information.

--
remove a 9 to reply by email

On Sep 28 2007, 05:42 pm, Erland Sommarskog <esquel@sommarskog.se> wrote in
news:Xns99B9F1E3FC5AEYazorman@127.0.0.1:

> By the way, I tried the repro on the July CTP of SQL 2008, and the error
> does not occur there, but the procedure completes successfully.

I hope it does not mean that they won't bother fixing it in 2005...

--
remove a 9 to reply by email