lock:timeout in tempdb

Why would we get a lock:timeout in tempdb?
MarkThe use of Global Tables, perhaps? What was locked?
Sincerely,
Anthony Thomas
"Mark Sullivan" wrote:

> Why would we get a lock:timeout in tempdb?
> Mark
>
>|||A table declared locally using declare. I am inserting into or reading from
this table and notice the lock:timeout in profiler. No other process is
using the table.
Mark
"AnthonyThomas" <AnthonyThomas@.discussions.microsoft.com> wrote in message
news:064E6C4F-DF33-4275-9AC8-9797484ECC9A@.microsoft.com...[vbcol=seagreen]
> The use of Global Tables, perhaps? What was locked?
> Sincerely,
>
> Anthony Thomas
>
> "Mark Sullivan" wrote:
>|||Lock Timeout is sort of a misnomer; it is really a Lock WAIT Timeout. The
only reason you get one is because your process had to wait for another to
complete so your's could place an incompatible lock on the object. You
waited too long; so, you got the timeout.
The question is what other process could have held a lock on a locally
declared temporary resource. Locally Declared does NOT mean a local
temporary object. That describes where you created it not the type.
#MyLocalTempTable is a local temp object; whereas, ##MyGlobalTempTable is
globally available, even if declared locally in scope.
If it was a local versus global temp object, the only other process that
could have held a lock would have been SQL Server itself. This would be
disturbing in that it took SQL Server so long to create some sort of work
object. You may have some hardware issues or there is something fishy about
the tempdb.
Sincerely,
Anthony Thomas
"Mark Sullivan" wrote:

> A table declared locally using declare. I am inserting into or reading fr
om
> this table and notice the lock:timeout in profiler. No other process is
> using the table.
> Mark
> "AnthonyThomas" <AnthonyThomas@.discussions.microsoft.com> wrote in message
> news:064E6C4F-DF33-4275-9AC8-9797484ECC9A@.microsoft.com...
>
>sql

lock:timeout in tempdb

Why would we get a lock:timeout in tempdb?
MarkThe use of Global Tables, perhaps? What was locked?
Sincerely,
Anthony Thomas
"Mark Sullivan" wrote:
> Why would we get a lock:timeout in tempdb?
> Mark
>
>|||A table declared locally using declare. I am inserting into or reading from
this table and notice the lock:timeout in profiler. No other process is
using the table.
Mark
"AnthonyThomas" <AnthonyThomas@.discussions.microsoft.com> wrote in message
news:064E6C4F-DF33-4275-9AC8-9797484ECC9A@.microsoft.com...
> The use of Global Tables, perhaps? What was locked?
> Sincerely,
>
> Anthony Thomas
>
> "Mark Sullivan" wrote:
> > Why would we get a lock:timeout in tempdb?
> >
> > Mark
> >
> >
> >|||Lock Timeout is sort of a misnomer; it is really a Lock WAIT Timeout. The
only reason you get one is because your process had to wait for another to
complete so your's could place an incompatible lock on the object. You
waited too long; so, you got the timeout.
The question is what other process could have held a lock on a locally
declared temporary resource. Locally Declared does NOT mean a local
temporary object. That describes where you created it not the type.
#MyLocalTempTable is a local temp object; whereas, ##MyGlobalTempTable is
globally available, even if declared locally in scope.
If it was a local versus global temp object, the only other process that
could have held a lock would have been SQL Server itself. This would be
disturbing in that it took SQL Server so long to create some sort of work
object. You may have some hardware issues or there is something fishy about
the tempdb.
Sincerely,
Anthony Thomas
"Mark Sullivan" wrote:
> A table declared locally using declare. I am inserting into or reading from
> this table and notice the lock:timeout in profiler. No other process is
> using the table.
> Mark
> "AnthonyThomas" <AnthonyThomas@.discussions.microsoft.com> wrote in message
> news:064E6C4F-DF33-4275-9AC8-9797484ECC9A@.microsoft.com...
> > The use of Global Tables, perhaps? What was locked?
> >
> > Sincerely,
> >
> >
> > Anthony Thomas
> >
> >
> > "Mark Sullivan" wrote:
> >
> > > Why would we get a lock:timeout in tempdb?
> > >
> > > Mark
> > >
> > >
> > >
>
>

lock:timeout in tempdb

Why would we get a lock:timeout in tempdb?
Mark
The use of Global Tables, perhaps? What was locked?
Sincerely,
Anthony Thomas
"Mark Sullivan" wrote:

> Why would we get a lock:timeout in tempdb?
> Mark
>
>
|||A table declared locally using declare. I am inserting into or reading from
this table and notice the lock:timeout in profiler. No other process is
using the table.
Mark
"AnthonyThomas" <AnthonyThomas@.discussions.microsoft.com> wrote in message
news:064E6C4F-DF33-4275-9AC8-9797484ECC9A@.microsoft.com...[vbcol=seagreen]
> The use of Global Tables, perhaps? What was locked?
> Sincerely,
>
> Anthony Thomas
>
> "Mark Sullivan" wrote:
|||Lock Timeout is sort of a misnomer; it is really a Lock WAIT Timeout. The
only reason you get one is because your process had to wait for another to
complete so your's could place an incompatible lock on the object. You
waited too long; so, you got the timeout.
The question is what other process could have held a lock on a locally
declared temporary resource. Locally Declared does NOT mean a local
temporary object. That describes where you created it not the type.
#MyLocalTempTable is a local temp object; whereas, ##MyGlobalTempTable is
globally available, even if declared locally in scope.
If it was a local versus global temp object, the only other process that
could have held a lock would have been SQL Server itself. This would be
disturbing in that it took SQL Server so long to create some sort of work
object. You may have some hardware issues or there is something fishy about
the tempdb.
Sincerely,
Anthony Thomas
"Mark Sullivan" wrote:

> A table declared locally using declare. I am inserting into or reading from
> this table and notice the lock:timeout in profiler. No other process is
> using the table.
> Mark
> "AnthonyThomas" <AnthonyThomas@.discussions.microsoft.com> wrote in message
> news:064E6C4F-DF33-4275-9AC8-9797484ECC9A@.microsoft.com...
>
>

Lock:Timeout + urgent

hi,
I am using SQL server 2000.and trying to resolve a deadlock issue.For
this i am looking at SQL profiler.and while turning the trace on, i
select Lock:Timeout event that needs to be traced.When done so, and
executing other requests, there appears an entry which just mentions
spid number, text data field is empty. I would like to know if after
lock times out,then the processes which are waiting for the lock to be
released, are they re tried again.That is if a process is waiting on a
lock,and lock times out,does SQL server tries to retry the same
process again?and if yes then after how much time interval?and is it
possible to set/change this time interval?

Thanks in advance
donThis is always helpful

http://www.sommarskog.se/sqlutil/aba_lockinfo.html

justwantananswer@.rediffmail.com (Don Nelson) wrote in message news:<dbd41fbb.0410210551.1b7f67e9@.posting.google.com>...
> hi,
> I am using SQL server 2000.and trying to resolve a deadlock issue.For
> this i am looking at SQL profiler.and while turning the trace on, i
> select Lock:Timeout event that needs to be traced.When done so, and
> executing other requests, there appears an entry which just mentions
> spid number, text data field is empty. I would like to know if after
> lock times out,then the processes which are waiting for the lock to be
> released, are they re tried again.That is if a process is waiting on a
> lock,and lock times out,does SQL server tries to retry the same
> process again?and if yes then after how much time interval?and is it
> possible to set/change this time interval?
> Thanks in advance
> don

lock timeouts

In our SQL Server, the Profiler is detecting the occaisional Lock:Timeout
event. I am assuming this means that some transaction is blocked, however
the @.@.LOCK_TIMEOUT value is set to -1 which means that locks never timeout.
So why am I detecting this event?
Any help much appreciated...This also includes internal lightweight locks that timeout and are not the
same as a real lock timeout. It's normal and not to be concerned with.
--
Andrew J. Kelly SQL MVP
"Mark" <swozz_@.hotmail.com> wrote in message
news:uzFOVYhjFHA.3960@.TK2MSFTNGP12.phx.gbl...
> In our SQL Server, the Profiler is detecting the occaisional Lock:Timeout
> event. I am assuming this means that some transaction is blocked, however
> the @.@.LOCK_TIMEOUT value is set to -1 which means that locks never
> timeout. So why am I detecting this event?
> Any help much appreciated...
>|||Are you talking about spinlocks here? Isn't it the case that spinlocks never
timeout? Anyway, during these timeouts, SQL Profiler does report an objectID
of 0 most of the time, but occaisionally the object ID is on one of the user
tables - do you still think we shouldn't be concerned with it?
--
Best regards
Mark Baldwin
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:%23QGbifmjFHA.2852@.TK2MSFTNGP15.phx.gbl...
> This also includes internal lightweight locks that timeout and are not the
> same as a real lock timeout. It's normal and not to be concerned with.
> --
> Andrew J. Kelly SQL MVP
>
> "Mark" <swozz_@.hotmail.com> wrote in message
> news:uzFOVYhjFHA.3960@.TK2MSFTNGP12.phx.gbl...
>> In our SQL Server, the Profiler is detecting the occaisional Lock:Timeout
>> event. I am assuming this means that some transaction is blocked, however
>> the @.@.LOCK_TIMEOUT value is set to -1 which means that locks never
>> timeout. So why am I detecting this event?
>> Any help much appreciated...
>|||It is hard to say how much of this is caused by the internal stuff or not.
Most likely it is nothing to worry about. But why are you looking at it in
the first place? Did you have an issue specific to locks or timeouts or
just curious? Unless you have an actual problem or other symptoms related
to this I wouldn't worry about it.
--
Andrew J. Kelly SQL MVP
"Mark" <swozz_@.hotmail.com> wrote in message
news:%234DOsVrjFHA.1044@.tk2msftngp13.phx.gbl...
> Are you talking about spinlocks here? Isn't it the case that spinlocks
> never timeout? Anyway, during these timeouts, SQL Profiler does report an
> objectID of 0 most of the time, but occaisionally the object ID is on one
> of the user tables - do you still think we shouldn't be concerned with it?
> --
> Best regards
> Mark Baldwin
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:%23QGbifmjFHA.2852@.TK2MSFTNGP15.phx.gbl...
>> This also includes internal lightweight locks that timeout and are not
>> the same as a real lock timeout. It's normal and not to be concerned
>> with.
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "Mark" <swozz_@.hotmail.com> wrote in message
>> news:uzFOVYhjFHA.3960@.TK2MSFTNGP12.phx.gbl...
>> In our SQL Server, the Profiler is detecting the occaisional
>> Lock:Timeout event. I am assuming this means that some transaction is
>> blocked, however the @.@.LOCK_TIMEOUT value is set to -1 which means that
>> locks never timeout. So why am I detecting this event?
>> Any help much appreciated...
>>
>|||We did have a deadlock issue which is now resolved but in the process in
investigating I stumbled on these timeouts messages. Yesterday, the web site
developer says he had one timeout issue, the profiler reported 7 timeout
issues on user data and around 40 timeout issues all together. I am just
trying to understand this Lock:Timeout message to determine if I can use it
to resolve and possibly find as yet unknown problems residing in the
database.
--
Best regards
Mark Baldwin
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:%23xWiDurjFHA.1444@.TK2MSFTNGP10.phx.gbl...
> It is hard to say how much of this is caused by the internal stuff or not.
> Most likely it is nothing to worry about. But why are you looking at it
> in the first place? Did you have an issue specific to locks or timeouts
> or just curious? Unless you have an actual problem or other symptoms
> related to this I wouldn't worry about it.
> --
> Andrew J. Kelly SQL MVP
>
> "Mark" <swozz_@.hotmail.com> wrote in message
> news:%234DOsVrjFHA.1044@.tk2msftngp13.phx.gbl...
>> Are you talking about spinlocks here? Isn't it the case that spinlocks
>> never timeout? Anyway, during these timeouts, SQL Profiler does report an
>> objectID of 0 most of the time, but occaisionally the object ID is on one
>> of the user tables - do you still think we shouldn't be concerned with
>> it?
>> --
>> Best regards
>> Mark Baldwin
>> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
>> news:%23QGbifmjFHA.2852@.TK2MSFTNGP15.phx.gbl...
>> This also includes internal lightweight locks that timeout and are not
>> the same as a real lock timeout. It's normal and not to be concerned
>> with.
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "Mark" <swozz_@.hotmail.com> wrote in message
>> news:uzFOVYhjFHA.3960@.TK2MSFTNGP12.phx.gbl...
>> In our SQL Server, the Profiler is detecting the occaisional
>> Lock:Timeout event. I am assuming this means that some transaction is
>> blocked, however the @.@.LOCK_TIMEOUT value is set to -1 which means that
>> locks never timeout. So why am I detecting this event?
>> Any help much appreciated...
>>
>>
>|||Mark wrote:
> We did have a deadlock issue which is now resolved but in the process
> in investigating I stumbled on these timeouts messages. Yesterday,
> the web site developer says he had one timeout issue, the profiler
> reported 7 timeout issues on user data and around 40 timeout issues
> all together. I am just trying to understand this Lock:Timeout
> message to determine if I can use it to resolve and possibly find as
> yet unknown problems residing in the database.
>
You can. But as Andrew stated, many of the Timeout errors are internal
lightweight timeouts. You may be able to use the Duration column to
determine if it is a relevant timeout.
--
David Gugick
Quest Software
www.imceda.com
www.quest.com

lock timeout

Hello All-
I've been troubleshooting deadlocks in a third-party app. I ran a trace with the Lock:Deadlock, Lock:Timeout, RPC:Starting, SQL Batch:Starting, and all Error and Warning events. I then imported the output files into a table for analysis. My results show 90,000+ Lock:Timeout events and 20 Lock:Deadlock events for a 5 hour period! The vast majority of the Lock:Timeout events are for index or table resources. The server is running SQL2K, sp3a.
Here's my questions:
-Shouldn't I be seeing corresponding Attention or Exception events for the Lock:Timeouts? I thought SQL Server returned an error 1229 to the client and terminated the statement on a lock timeout. Would I not see this in the trace? Do I really have 90,000+ statements being terminated?
-a small number of the database ids in the Lock:Timeout events are large ids not included in sysdatabases. Where are these coming from? A bug?
-My developers swear that they are not setting a lock_timeout on their connections. Nor can I find a SET LOCK_TIMEOUT statement in the trace output. So why am I getting all these timeouts? I thought SQL Server by default waited indefinitely on a lock.
I confused! Any insight would be greatly appreciated.
thanks-
MargaretUnfortunately that counter is pretty much useless since it includes internal
lightweight timeouts that are normal and always present. You pretty much
have to forget about the majority of them as normal.
--
Andrew J. Kelly SQL MVP
"Margaret" <anonymous@.discussions.microsoft.com> wrote in message
news:57AEA811-A059-4426-B023-5FD611C7D330@.microsoft.com...
> Hello All-
> I've been troubleshooting deadlocks in a third-party app. I ran a trace
with the Lock:Deadlock, Lock:Timeout, RPC:Starting, SQL Batch:Starting, and
all Error and Warning events. I then imported the output files into a table
for analysis. My results show 90,000+ Lock:Timeout events and 20
Lock:Deadlock events for a 5 hour period! The vast majority of the
Lock:Timeout events are for index or table resources. The server is running
SQL2K, sp3a.
> Here's my questions:
> -Shouldn't I be seeing corresponding Attention or Exception events for the
Lock:Timeouts? I thought SQL Server returned an error 1229 to the client
and terminated the statement on a lock timeout. Would I not see this in the
trace? Do I really have 90,000+ statements being terminated?
> -a small number of the database ids in the Lock:Timeout events are large
ids not included in sysdatabases. Where are these coming from? A bug?
> -My developers swear that they are not setting a lock_timeout on their
connections. Nor can I find a SET LOCK_TIMEOUT statement in the trace
output. So why am I getting all these timeouts? I thought SQL Server by
default waited indefinitely on a lock.
> I confused! Any insight would be greatly appreciated.
> thanks-
> Margaret
>