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

Dimitri Furman (dfurman@cloud99.net) writes:
> 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...

I could be that it's fixed in Katmai "by accident", but it could also
be the case that the issue is known, but the fix is deemed to risky to
implement in SQL 2005.

I found this KB Article: http://support.microsoft.com/kb/923605, but this
appears to relate to a fix for SP1. Nevertheless, the Symptoms section
says "This problem occurs if table variables are used in a transaction."


--
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