Can you explain the lock information output from
sysprocesses table that I received?
SPID waittype waittime lastwaittype
waitresources
155 0x0000 0 LCK_M_S KEY: 7:1:1 (b400b60e9149)
160 0x0000 0 PAGELATCH_UP 2:1:31965
166 0x0000 0 PAGEIOLATCH_SH 7:1:523911
183 0x0000 0 PAGELATCH_UP 2:1:31988
198 0x0000 0 LCK_M_S KEY: 7:1:1 (b400b60e9149)
216 0x0000 0 LCK_M_S KEY: 7:1:1 (8b00c4cca785)
217 0x0000 0 PAGELATCH_UP 2:1:31984
218 0x0000 0 PAGEIOLATCH_SH 7:1:1395322
219 0x0000 0 PAGEIOLATCH_SH 7:1:642128
228 0x0000 0 PAGEIOLATCH_SH 7:1:1605295
230 0x0000 0 PAGEIOLATCH_SH 7:1:1587592
Thank You,
MikeHi Mike
Right now it is showing that none of your processes are waiting for
anything. All of them have a waittype and waittime of 0 which means they are
NOT waiting. THe lastwaittype column, as the name implies, is the last thing
the process waited on, but there is no indication of how long ago or what
duration that wait was. The LCK-* waits are normal locks being waited for,
and the PAGEIOLATCH* waits are waiting for IO.
This looks totally boring and nothing to worry about.
Did you have some specific concerns about it?
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Mike" <anonymous@.discussions.microsoft.com> wrote in message
news:1886001c41b62$9579a9e0$a301280a@.phx
.gbl...
> Can you explain the lock information output from
> sysprocesses table that I received?
> SPID waittype waittime lastwaittype
> waitresources
> 155 0x0000 0 LCK_M_S KEY: 7:1:1 (b400b60e9149)
> 160 0x0000 0 PAGELATCH_UP 2:1:31965
> 166 0x0000 0 PAGEIOLATCH_SH 7:1:523911
> 183 0x0000 0 PAGELATCH_UP 2:1:31988
> 198 0x0000 0 LCK_M_S KEY: 7:1:1 (b400b60e9149)
> 216 0x0000 0 LCK_M_S KEY: 7:1:1 (8b00c4cca785)
> 217 0x0000 0 PAGELATCH_UP 2:1:31984
> 218 0x0000 0 PAGEIOLATCH_SH 7:1:1395322
> 219 0x0000 0 PAGEIOLATCH_SH 7:1:642128
> 228 0x0000 0 PAGEIOLATCH_SH 7:1:1605295
> 230 0x0000 0 PAGEIOLATCH_SH 7:1:1587592
> Thank You,
> Mike
Showing posts with label lock. Show all posts
Showing posts with label lock. Show all posts
Monday, February 20, 2012
Locks Option - Need advanced help
I am having a problem with lock resources on my server. I am getting
Error: 1204 Cannot obtain LOCK resource at this time.
Now, before you jump to any quick answers, please keep reading.
SQL2K SP3, 8 GB RAM, AWE Enabled, Min Server memory = 1024 MB, Max
Server memory = 7168 MB
When I had the server set to dynamically configure the locks, I would
get error 1204 after my Lock memory increased to 985,728 KB. It would
increase steadily, but once it got to 985,728 KB - it would hit a wall
and not increase any further. It is my understanding that lock memory
should allocate up to 40% of the total server memory. This number
represents only about 13% of the total server memory allocated at the
time.
Considering I could not get enough resources allocated, I tried to do
the math myself and manually configure the locks option on the server
to a value of 34,000,000. Considering each lock represents 96 bytes,
this number should be about 40% of my total server memory.
Problem is that when my server starts, I get the following error:
Can't allocate 34000000 locks on startup, reverting to 4309162, (25%
of committed memory)
Where is that coming from? Why is it allocating only 25% and 25% of
what? Certainly not 7168 MB. It is my understanding that when AWE is
enabled, it reserves the maximum server memory as soon as SQL starts.
Also, when I configure the lock option manually, I thought I was just
configuring the maximum it could get to, not the value that it would
reserve all the time, so it should not try to allocate that much at
startup.
I would love to use the dynamic configuration of locks if it would
wouldn't get stuck at a maximum of 13% of server memory.
Please help. Thanks.I could be wrong but I believe that locks are one of the many things in
memory that can not live in the AWE memory space. That would explain why
you can't go higher. That's a lot of memory for locks. I would look into
why you are taking so many locks as that is the real root of the problem.
--
Andrew J. Kelly
SQL Server MVP
"Jeff Albenberg" <jalbenberg@.yahoo.com> wrote in message
news:e9dc0a21.0310281557.6785de18@.posting.google.com...
> I am having a problem with lock resources on my server. I am getting
> Error: 1204 Cannot obtain LOCK resource at this time.
> Now, before you jump to any quick answers, please keep reading.
> SQL2K SP3, 8 GB RAM, AWE Enabled, Min Server memory = 1024 MB, Max
> Server memory = 7168 MB
> When I had the server set to dynamically configure the locks, I would
> get error 1204 after my Lock memory increased to 985,728 KB. It would
> increase steadily, but once it got to 985,728 KB - it would hit a wall
> and not increase any further. It is my understanding that lock memory
> should allocate up to 40% of the total server memory. This number
> represents only about 13% of the total server memory allocated at the
> time.
> Considering I could not get enough resources allocated, I tried to do
> the math myself and manually configure the locks option on the server
> to a value of 34,000,000. Considering each lock represents 96 bytes,
> this number should be about 40% of my total server memory.
> Problem is that when my server starts, I get the following error:
> Can't allocate 34000000 locks on startup, reverting to 4309162, (25%
> of committed memory)
> Where is that coming from? Why is it allocating only 25% and 25% of
> what? Certainly not 7168 MB. It is my understanding that when AWE is
> enabled, it reserves the maximum server memory as soon as SQL starts.
> Also, when I configure the lock option manually, I thought I was just
> configuring the maximum it could get to, not the value that it would
> reserve all the time, so it should not try to allocate that much at
> startup.
> I would love to use the dynamic configuration of locks if it would
> wouldn't get stuck at a maximum of 13% of server memory.
> Please help. Thanks.|||Andrew is right. Locks can not be allocated from the AWE space. The lock
manager constrains the amount of memory it consumes based upon the committed
size of the buffer pool. There is a perfmon counter that will give you the
buffer manager's committed size.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:u0ODNDbnDHA.2068@.TK2MSFTNGP09.phx.gbl...
> I could be wrong but I believe that locks are one of the many things in
> memory that can not live in the AWE memory space. That would explain why
> you can't go higher. That's a lot of memory for locks. I would look into
> why you are taking so many locks as that is the real root of the problem.
>|||what do you mean by committed size of the buffer pool ?
"David Campbell" <dave_gc_nospam@.hotmail.com> wrote in message
news:vpuekj1v7dot38@.corp.supernews.com...
> Andrew is right. Locks can not be allocated from the AWE space. The lock
> manager constrains the amount of memory it consumes based upon the
committed
> size of the buffer pool. There is a perfmon counter that will give you the
> buffer manager's committed size.
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:u0ODNDbnDHA.2068@.TK2MSFTNGP09.phx.gbl...
> > I could be wrong but I believe that locks are one of the many things in
> > memory that can not live in the AWE memory space. That would explain
why
> > you can't go higher. That's a lot of memory for locks. I would look
into
> > why you are taking so many locks as that is the real root of the
problem.
> >
>|||Jeff, Still struggling eh? You asked yourself 25% of what. Isn't it simply
25% of min server memory? I think that comes very lcose to 64 bytes per lock
(64*4309162*4(=25%)) is about 1024MB, your 'min server mem'.
Locks are 64 byte structures, a lock owner adds 32 bytes, and there is also
a lock hash slot structure with 8 byte per lock(as far as I could see). So
maybe by combining some of these other 32 and 8 byte numbers, sql comes up
with the 4309162 locks
I would think that increasing the 'min server memory' could solve your
problem, but that's (an educated) guess.
As the other authors say, SQLserver doesn't allocate lock structures from
AWE memory. I think because of the implementation of AWE, it is to 'clumsy'
to use it for other things than database page buffers.
regards,
Mario
http://www.sqlinternals.com
"Jeff Albenberg" <jalbenberg@.yahoo.com> wrote in message
news:e9dc0a21.0310281557.6785de18@.posting.google.com...
> I am having a problem with lock resources on my server. I am getting
> Error: 1204 Cannot obtain LOCK resource at this time.
> Now, before you jump to any quick answers, please keep reading.
> SQL2K SP3, 8 GB RAM, AWE Enabled, Min Server memory = 1024 MB, Max
> Server memory = 7168 MB
> When I had the server set to dynamically configure the locks, I would
> get error 1204 after my Lock memory increased to 985,728 KB. It would
> increase steadily, but once it got to 985,728 KB - it would hit a wall
> and not increase any further. It is my understanding that lock memory
> should allocate up to 40% of the total server memory. This number
> represents only about 13% of the total server memory allocated at the
> time.
> Considering I could not get enough resources allocated, I tried to do
> the math myself and manually configure the locks option on the server
> to a value of 34,000,000. Considering each lock represents 96 bytes,
> this number should be about 40% of my total server memory.
> Problem is that when my server starts, I get the following error:
> Can't allocate 34000000 locks on startup, reverting to 4309162, (25%
> of committed memory)
> Where is that coming from? Why is it allocating only 25% and 25% of
> what? Certainly not 7168 MB. It is my understanding that when AWE is
> enabled, it reserves the maximum server memory as soon as SQL starts.
> Also, when I configure the lock option manually, I thought I was just
> configuring the maximum it could get to, not the value that it would
> reserve all the time, so it should not try to allocate that much at
> startup.
> I would love to use the dynamic configuration of locks if it would
> wouldn't get stuck at a maximum of 13% of server memory.
> Please help. Thanks.|||Certainly don't want to put words in David's mouth but I believe he is
referring to the amount of memory in the buffer pool that is actually being
used. Just because you set the max memory to a certain size doesn't mean it
is actually holding data in all those buffer slots.
--
Andrew J. Kelly
SQL Server MVP
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:uA96d8dnDHA.2404@.TK2MSFTNGP12.phx.gbl...
> what do you mean by committed size of the buffer pool ?
> "David Campbell" <dave_gc_nospam@.hotmail.com> wrote in message
> news:vpuekj1v7dot38@.corp.supernews.com...
> > Andrew is right. Locks can not be allocated from the AWE space. The lock
> > manager constrains the amount of memory it consumes based upon the
> committed
> > size of the buffer pool. There is a perfmon counter that will give you
the
> > buffer manager's committed size.
> >
> >
> > "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> > news:u0ODNDbnDHA.2068@.TK2MSFTNGP09.phx.gbl...
> > > I could be wrong but I believe that locks are one of the many things
in
> > > memory that can not live in the AWE memory space. That would explain
> why
> > > you can't go higher. That's a lot of memory for locks. I would look
> into
> > > why you are taking so many locks as that is the real root of the
> problem.
> > >
> >
> >
>|||"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:uA96d8dnDHA.2404@.TK2MSFTNGP12.phx.gbl...
> what do you mean by committed size of the buffer pool ?
SQL Server's buffer manager allocates the bulk of a processes *VIRTUAL*
address space on startup and then controls the amount of committed memory it
consumes by using the VirtualAlloc API with the MEM_COMMIT flag. This is how
SQL Server dynamically grows and shrinks its memory in response to system
demand.
The "committed" size of the buffer pool is the amount of memory the buffer
pool has committed at that point.
I agree with Andrew in thinking the interesting question is why the process
is consuming so many locks.
Error: 1204 Cannot obtain LOCK resource at this time.
Now, before you jump to any quick answers, please keep reading.
SQL2K SP3, 8 GB RAM, AWE Enabled, Min Server memory = 1024 MB, Max
Server memory = 7168 MB
When I had the server set to dynamically configure the locks, I would
get error 1204 after my Lock memory increased to 985,728 KB. It would
increase steadily, but once it got to 985,728 KB - it would hit a wall
and not increase any further. It is my understanding that lock memory
should allocate up to 40% of the total server memory. This number
represents only about 13% of the total server memory allocated at the
time.
Considering I could not get enough resources allocated, I tried to do
the math myself and manually configure the locks option on the server
to a value of 34,000,000. Considering each lock represents 96 bytes,
this number should be about 40% of my total server memory.
Problem is that when my server starts, I get the following error:
Can't allocate 34000000 locks on startup, reverting to 4309162, (25%
of committed memory)
Where is that coming from? Why is it allocating only 25% and 25% of
what? Certainly not 7168 MB. It is my understanding that when AWE is
enabled, it reserves the maximum server memory as soon as SQL starts.
Also, when I configure the lock option manually, I thought I was just
configuring the maximum it could get to, not the value that it would
reserve all the time, so it should not try to allocate that much at
startup.
I would love to use the dynamic configuration of locks if it would
wouldn't get stuck at a maximum of 13% of server memory.
Please help. Thanks.I could be wrong but I believe that locks are one of the many things in
memory that can not live in the AWE memory space. That would explain why
you can't go higher. That's a lot of memory for locks. I would look into
why you are taking so many locks as that is the real root of the problem.
--
Andrew J. Kelly
SQL Server MVP
"Jeff Albenberg" <jalbenberg@.yahoo.com> wrote in message
news:e9dc0a21.0310281557.6785de18@.posting.google.com...
> I am having a problem with lock resources on my server. I am getting
> Error: 1204 Cannot obtain LOCK resource at this time.
> Now, before you jump to any quick answers, please keep reading.
> SQL2K SP3, 8 GB RAM, AWE Enabled, Min Server memory = 1024 MB, Max
> Server memory = 7168 MB
> When I had the server set to dynamically configure the locks, I would
> get error 1204 after my Lock memory increased to 985,728 KB. It would
> increase steadily, but once it got to 985,728 KB - it would hit a wall
> and not increase any further. It is my understanding that lock memory
> should allocate up to 40% of the total server memory. This number
> represents only about 13% of the total server memory allocated at the
> time.
> Considering I could not get enough resources allocated, I tried to do
> the math myself and manually configure the locks option on the server
> to a value of 34,000,000. Considering each lock represents 96 bytes,
> this number should be about 40% of my total server memory.
> Problem is that when my server starts, I get the following error:
> Can't allocate 34000000 locks on startup, reverting to 4309162, (25%
> of committed memory)
> Where is that coming from? Why is it allocating only 25% and 25% of
> what? Certainly not 7168 MB. It is my understanding that when AWE is
> enabled, it reserves the maximum server memory as soon as SQL starts.
> Also, when I configure the lock option manually, I thought I was just
> configuring the maximum it could get to, not the value that it would
> reserve all the time, so it should not try to allocate that much at
> startup.
> I would love to use the dynamic configuration of locks if it would
> wouldn't get stuck at a maximum of 13% of server memory.
> Please help. Thanks.|||Andrew is right. Locks can not be allocated from the AWE space. The lock
manager constrains the amount of memory it consumes based upon the committed
size of the buffer pool. There is a perfmon counter that will give you the
buffer manager's committed size.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:u0ODNDbnDHA.2068@.TK2MSFTNGP09.phx.gbl...
> I could be wrong but I believe that locks are one of the many things in
> memory that can not live in the AWE memory space. That would explain why
> you can't go higher. That's a lot of memory for locks. I would look into
> why you are taking so many locks as that is the real root of the problem.
>|||what do you mean by committed size of the buffer pool ?
"David Campbell" <dave_gc_nospam@.hotmail.com> wrote in message
news:vpuekj1v7dot38@.corp.supernews.com...
> Andrew is right. Locks can not be allocated from the AWE space. The lock
> manager constrains the amount of memory it consumes based upon the
committed
> size of the buffer pool. There is a perfmon counter that will give you the
> buffer manager's committed size.
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:u0ODNDbnDHA.2068@.TK2MSFTNGP09.phx.gbl...
> > I could be wrong but I believe that locks are one of the many things in
> > memory that can not live in the AWE memory space. That would explain
why
> > you can't go higher. That's a lot of memory for locks. I would look
into
> > why you are taking so many locks as that is the real root of the
problem.
> >
>|||Jeff, Still struggling eh? You asked yourself 25% of what. Isn't it simply
25% of min server memory? I think that comes very lcose to 64 bytes per lock
(64*4309162*4(=25%)) is about 1024MB, your 'min server mem'.
Locks are 64 byte structures, a lock owner adds 32 bytes, and there is also
a lock hash slot structure with 8 byte per lock(as far as I could see). So
maybe by combining some of these other 32 and 8 byte numbers, sql comes up
with the 4309162 locks
I would think that increasing the 'min server memory' could solve your
problem, but that's (an educated) guess.
As the other authors say, SQLserver doesn't allocate lock structures from
AWE memory. I think because of the implementation of AWE, it is to 'clumsy'
to use it for other things than database page buffers.
regards,
Mario
http://www.sqlinternals.com
"Jeff Albenberg" <jalbenberg@.yahoo.com> wrote in message
news:e9dc0a21.0310281557.6785de18@.posting.google.com...
> I am having a problem with lock resources on my server. I am getting
> Error: 1204 Cannot obtain LOCK resource at this time.
> Now, before you jump to any quick answers, please keep reading.
> SQL2K SP3, 8 GB RAM, AWE Enabled, Min Server memory = 1024 MB, Max
> Server memory = 7168 MB
> When I had the server set to dynamically configure the locks, I would
> get error 1204 after my Lock memory increased to 985,728 KB. It would
> increase steadily, but once it got to 985,728 KB - it would hit a wall
> and not increase any further. It is my understanding that lock memory
> should allocate up to 40% of the total server memory. This number
> represents only about 13% of the total server memory allocated at the
> time.
> Considering I could not get enough resources allocated, I tried to do
> the math myself and manually configure the locks option on the server
> to a value of 34,000,000. Considering each lock represents 96 bytes,
> this number should be about 40% of my total server memory.
> Problem is that when my server starts, I get the following error:
> Can't allocate 34000000 locks on startup, reverting to 4309162, (25%
> of committed memory)
> Where is that coming from? Why is it allocating only 25% and 25% of
> what? Certainly not 7168 MB. It is my understanding that when AWE is
> enabled, it reserves the maximum server memory as soon as SQL starts.
> Also, when I configure the lock option manually, I thought I was just
> configuring the maximum it could get to, not the value that it would
> reserve all the time, so it should not try to allocate that much at
> startup.
> I would love to use the dynamic configuration of locks if it would
> wouldn't get stuck at a maximum of 13% of server memory.
> Please help. Thanks.|||Certainly don't want to put words in David's mouth but I believe he is
referring to the amount of memory in the buffer pool that is actually being
used. Just because you set the max memory to a certain size doesn't mean it
is actually holding data in all those buffer slots.
--
Andrew J. Kelly
SQL Server MVP
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:uA96d8dnDHA.2404@.TK2MSFTNGP12.phx.gbl...
> what do you mean by committed size of the buffer pool ?
> "David Campbell" <dave_gc_nospam@.hotmail.com> wrote in message
> news:vpuekj1v7dot38@.corp.supernews.com...
> > Andrew is right. Locks can not be allocated from the AWE space. The lock
> > manager constrains the amount of memory it consumes based upon the
> committed
> > size of the buffer pool. There is a perfmon counter that will give you
the
> > buffer manager's committed size.
> >
> >
> > "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> > news:u0ODNDbnDHA.2068@.TK2MSFTNGP09.phx.gbl...
> > > I could be wrong but I believe that locks are one of the many things
in
> > > memory that can not live in the AWE memory space. That would explain
> why
> > > you can't go higher. That's a lot of memory for locks. I would look
> into
> > > why you are taking so many locks as that is the real root of the
> problem.
> > >
> >
> >
>|||"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:uA96d8dnDHA.2404@.TK2MSFTNGP12.phx.gbl...
> what do you mean by committed size of the buffer pool ?
SQL Server's buffer manager allocates the bulk of a processes *VIRTUAL*
address space on startup and then controls the amount of committed memory it
consumes by using the VirtualAlloc API with the MEM_COMMIT flag. This is how
SQL Server dynamically grows and shrinks its memory in response to system
demand.
The "committed" size of the buffer pool is the amount of memory the buffer
pool has committed at that point.
I agree with Andrew in thinking the interesting question is why the process
is consuming so many locks.
locks on table when update statistics
Does anybody know what kind of lock will put on the table
when sqlserver update the statistics?Only a schema stability lock, which means that you can't change the table,
but you can do everything else, and the statistics update won't block
anything.
--
Jacco Schalkwijk
SQL Server MVP
"jzhu" <hzhua16@.hotmail.com> wrote in message
news:03ef01c39e54$197dc460$a101280a@.phx.gbl...
> Does anybody know what kind of lock will put on the table
> when sqlserver update the statistics?
when sqlserver update the statistics?Only a schema stability lock, which means that you can't change the table,
but you can do everything else, and the statistics update won't block
anything.
--
Jacco Schalkwijk
SQL Server MVP
"jzhu" <hzhua16@.hotmail.com> wrote in message
news:03ef01c39e54$197dc460$a101280a@.phx.gbl...
> Does anybody know what kind of lock will put on the table
> when sqlserver update the statistics?
locks on object "xp_startmail"
Hy,
recently I got a problem with locks on my sql server 2000
SP3. I took a trace and I could see that there are a lot
of lock timeout (sometimes deadlock, but not regularly)
when the load is heavy on the sql server machine.
I took a trace and tryed to understand the trace result,
and I found that almost always the lock were on an
objectid (309576141) not corresponding to a table or an
index but to a store procedure : xp_sendmail ! I was very
astonished because this seems to be a procedure for
sending mail (I'm not sending any mail in my sql server
application). Moreover the SPID timing out is often a
task manager process.
Now, I would like to know why I can't see the real object
on which the process is timed out OR if this kind of lock
hides some other problems I'd better face.
Many thanks for any kind of suggestion.
cristina
Sorry for the basic question, but did you check for the object id in the correct database?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"cris" <anonymous@.discussions.microsoft.com> wrote in message news:1fe201c44a4b$32290c10$7d02280a@.phx.gbl...
> Hy,
> recently I got a problem with locks on my sql server 2000
> SP3. I took a trace and I could see that there are a lot
> of lock timeout (sometimes deadlock, but not regularly)
> when the load is heavy on the sql server machine.
> I took a trace and tryed to understand the trace result,
> and I found that almost always the lock were on an
> objectid (309576141) not corresponding to a table or an
> index but to a store procedure : xp_sendmail ! I was very
> astonished because this seems to be a procedure for
> sending mail (I'm not sending any mail in my sql server
> application). Moreover the SPID timing out is often a
> task manager process.
> Now, I would like to know why I can't see the real object
> on which the process is timed out OR if this kind of lock
> hides some other problems I'd better face.
> Many thanks for any kind of suggestion.
> cristina
|||Thankyou very much for your suggestion!
I feel very ashamed about my error. I was checking in the 'master' database!
Escuse me, but I made a blunder, and without your help I couldn't get out of this error...
recently I got a problem with locks on my sql server 2000
SP3. I took a trace and I could see that there are a lot
of lock timeout (sometimes deadlock, but not regularly)
when the load is heavy on the sql server machine.
I took a trace and tryed to understand the trace result,
and I found that almost always the lock were on an
objectid (309576141) not corresponding to a table or an
index but to a store procedure : xp_sendmail ! I was very
astonished because this seems to be a procedure for
sending mail (I'm not sending any mail in my sql server
application). Moreover the SPID timing out is often a
task manager process.
Now, I would like to know why I can't see the real object
on which the process is timed out OR if this kind of lock
hides some other problems I'd better face.
Many thanks for any kind of suggestion.
cristina
Sorry for the basic question, but did you check for the object id in the correct database?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"cris" <anonymous@.discussions.microsoft.com> wrote in message news:1fe201c44a4b$32290c10$7d02280a@.phx.gbl...
> Hy,
> recently I got a problem with locks on my sql server 2000
> SP3. I took a trace and I could see that there are a lot
> of lock timeout (sometimes deadlock, but not regularly)
> when the load is heavy on the sql server machine.
> I took a trace and tryed to understand the trace result,
> and I found that almost always the lock were on an
> objectid (309576141) not corresponding to a table or an
> index but to a store procedure : xp_sendmail ! I was very
> astonished because this seems to be a procedure for
> sending mail (I'm not sending any mail in my sql server
> application). Moreover the SPID timing out is often a
> task manager process.
> Now, I would like to know why I can't see the real object
> on which the process is timed out OR if this kind of lock
> hides some other problems I'd better face.
> Many thanks for any kind of suggestion.
> cristina
|||Thankyou very much for your suggestion!
I feel very ashamed about my error. I was checking in the 'master' database!
Escuse me, but I made a blunder, and without your help I couldn't get out of this error...
locks on object "xp_startmail"
Hy,
recently I got a problem with locks on my sql server 2000
SP3. I took a trace and I could see that there are a lot
of lock timeout (sometimes deadlock, but not regularly)
when the load is heavy on the sql server machine.
I took a trace and tryed to understand the trace result,
and I found that almost always the lock were on an
objectid (309576141) not corresponding to a table or an
index but to a store procedure : xp_sendmail ! I was very
astonished because this seems to be a procedure for
sending mail (I'm not sending any mail in my sql server
application). Moreover the SPID timing out is often a
task manager process.
Now, I would like to know why I can't see the real object
on which the process is timed out OR if this kind of lock
hides some other problems I'd better face.
Many thanks for any kind of suggestion.
cristinaSorry for the basic question, but did you check for the object id in the correct database?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"cris" <anonymous@.discussions.microsoft.com> wrote in message news:1fe201c44a4b$32290c10$7d02280a@.phx.gbl...
> Hy,
> recently I got a problem with locks on my sql server 2000
> SP3. I took a trace and I could see that there are a lot
> of lock timeout (sometimes deadlock, but not regularly)
> when the load is heavy on the sql server machine.
> I took a trace and tryed to understand the trace result,
> and I found that almost always the lock were on an
> objectid (309576141) not corresponding to a table or an
> index but to a store procedure : xp_sendmail ! I was very
> astonished because this seems to be a procedure for
> sending mail (I'm not sending any mail in my sql server
> application). Moreover the SPID timing out is often a
> task manager process.
> Now, I would like to know why I can't see the real object
> on which the process is timed out OR if this kind of lock
> hides some other problems I'd better face.
> Many thanks for any kind of suggestion.
> cristina|||Thankyou very much for your suggestion!
I feel very ashamed about my error. I was checking in the 'master' database
Escuse me, but I made a blunder, and without your help I couldn't get out of this error..
recently I got a problem with locks on my sql server 2000
SP3. I took a trace and I could see that there are a lot
of lock timeout (sometimes deadlock, but not regularly)
when the load is heavy on the sql server machine.
I took a trace and tryed to understand the trace result,
and I found that almost always the lock were on an
objectid (309576141) not corresponding to a table or an
index but to a store procedure : xp_sendmail ! I was very
astonished because this seems to be a procedure for
sending mail (I'm not sending any mail in my sql server
application). Moreover the SPID timing out is often a
task manager process.
Now, I would like to know why I can't see the real object
on which the process is timed out OR if this kind of lock
hides some other problems I'd better face.
Many thanks for any kind of suggestion.
cristinaSorry for the basic question, but did you check for the object id in the correct database?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"cris" <anonymous@.discussions.microsoft.com> wrote in message news:1fe201c44a4b$32290c10$7d02280a@.phx.gbl...
> Hy,
> recently I got a problem with locks on my sql server 2000
> SP3. I took a trace and I could see that there are a lot
> of lock timeout (sometimes deadlock, but not regularly)
> when the load is heavy on the sql server machine.
> I took a trace and tryed to understand the trace result,
> and I found that almost always the lock were on an
> objectid (309576141) not corresponding to a table or an
> index but to a store procedure : xp_sendmail ! I was very
> astonished because this seems to be a procedure for
> sending mail (I'm not sending any mail in my sql server
> application). Moreover the SPID timing out is often a
> task manager process.
> Now, I would like to know why I can't see the real object
> on which the process is timed out OR if this kind of lock
> hides some other problems I'd better face.
> Many thanks for any kind of suggestion.
> cristina|||Thankyou very much for your suggestion!
I feel very ashamed about my error. I was checking in the 'master' database
Escuse me, but I made a blunder, and without your help I couldn't get out of this error..
locks on object "xp_startmail"
Hy,
recently I got a problem with locks on my sql server 2000
SP3. I took a trace and I could see that there are a lot
of lock timeout (sometimes deadlock, but not regularly)
when the load is heavy on the sql server machine.
I took a trace and tryed to understand the trace result,
and I found that almost always the lock were on an
objectid (309576141) not corresponding to a table or an
index but to a store procedure : xp_sendmail ! I was very
astonished because this seems to be a procedure for
sending mail (I'm not sending any mail in my sql server
application). Moreover the SPID timing out is often a
task manager process.
Now, I would like to know why I can't see the real object
on which the process is timed out OR if this kind of lock
hides some other problems I'd better face.
Many thanks for any kind of suggestion.
cristinaSorry for the basic question, but did you check for the object id in the cor
rect database?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"cris" <anonymous@.discussions.microsoft.com> wrote in message news:1fe201c44a4b$32290c10$7d0
2280a@.phx.gbl...
> Hy,
> recently I got a problem with locks on my sql server 2000
> SP3. I took a trace and I could see that there are a lot
> of lock timeout (sometimes deadlock, but not regularly)
> when the load is heavy on the sql server machine.
> I took a trace and tryed to understand the trace result,
> and I found that almost always the lock were on an
> objectid (309576141) not corresponding to a table or an
> index but to a store procedure : xp_sendmail ! I was very
> astonished because this seems to be a procedure for
> sending mail (I'm not sending any mail in my sql server
> application). Moreover the SPID timing out is often a
> task manager process.
> Now, I would like to know why I can't see the real object
> on which the process is timed out OR if this kind of lock
> hides some other problems I'd better face.
> Many thanks for any kind of suggestion.
> cristina|||Thankyou very much for your suggestion!
I feel very ashamed about my error. I was checking in the 'master' database!
Escuse me, but I made a blunder, and without your help I couldn't get out o
f this error...
recently I got a problem with locks on my sql server 2000
SP3. I took a trace and I could see that there are a lot
of lock timeout (sometimes deadlock, but not regularly)
when the load is heavy on the sql server machine.
I took a trace and tryed to understand the trace result,
and I found that almost always the lock were on an
objectid (309576141) not corresponding to a table or an
index but to a store procedure : xp_sendmail ! I was very
astonished because this seems to be a procedure for
sending mail (I'm not sending any mail in my sql server
application). Moreover the SPID timing out is often a
task manager process.
Now, I would like to know why I can't see the real object
on which the process is timed out OR if this kind of lock
hides some other problems I'd better face.
Many thanks for any kind of suggestion.
cristinaSorry for the basic question, but did you check for the object id in the cor
rect database?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"cris" <anonymous@.discussions.microsoft.com> wrote in message news:1fe201c44a4b$32290c10$7d0
2280a@.phx.gbl...
> Hy,
> recently I got a problem with locks on my sql server 2000
> SP3. I took a trace and I could see that there are a lot
> of lock timeout (sometimes deadlock, but not regularly)
> when the load is heavy on the sql server machine.
> I took a trace and tryed to understand the trace result,
> and I found that almost always the lock were on an
> objectid (309576141) not corresponding to a table or an
> index but to a store procedure : xp_sendmail ! I was very
> astonished because this seems to be a procedure for
> sending mail (I'm not sending any mail in my sql server
> application). Moreover the SPID timing out is often a
> task manager process.
> Now, I would like to know why I can't see the real object
> on which the process is timed out OR if this kind of lock
> hides some other problems I'd better face.
> Many thanks for any kind of suggestion.
> cristina|||Thankyou very much for your suggestion!
I feel very ashamed about my error. I was checking in the 'master' database!
Escuse me, but I made a blunder, and without your help I couldn't get out o
f this error...
Locks in SQL2000
How to lock a Row in SQL2000 so that nobody can select that row.
I applied ROWLOCK, but i am not finding the way.
My query is"SELECT * FROM tablename WITH (ROWLOCK)"
Is this the correct way to write locks.
I would be thankful if u help me
Hi,
Maybe the following link is helpful to you.
Row-Level Locking Using ADO & SQL Server http://support.microsoft.com/kb/252317
Thanks.
|||
Hi all,
I am using SQLServer 2000 & .Net 2003 (1.1 framework)
I had a database calledDATA and the fields ID, Name, Remarks, etc...
I need to give access all the data to my employees in their browser machines.
Number of employees may be from 50 to 150.
If a particular record is fetched by 2 users simultaneously one person is getting an error. For this i need to put locks in the database.
If a particular record is fetched by 1 user, then immediately the another employee who is accessing it, should get another record/data.
Being keeping in mind, how can i solve this scnario?
Any body please help me...
Regards,
Jai Shankar
Subscribe to:
Posts (Atom)