The Current Activity monitor (Locks/Object) shows a number of processes that
have obtained a database level lock.
Details: (Mode:Shared, Status:GRANT, Owner:SESSION)
What causes these db level locks?
All are SHARED level locks - and the status for is GRANT - meaning that the
lock has been successfully obtained.
How can I prevent these locks from appearing?
Cheers!
SQLCatZHi
Every connection will acquire a DB level lock when the connection is
established, to the database specified in the connection string from the
client..
This enables SQL Server to do a very fast lookup to see if a DB is in use
(for auto close, detach, drop database etc).
It is a shared lock.
You can not set this.
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"SQLCatz" <SQLCatz@.discussions.microsoft.com> wrote in message
news:DA43536F-E52A-4D1A-AB83-6433FEA6D1BD@.microsoft.com...
> The Current Activity monitor (Locks/Object) shows a number of processes
> that
> have obtained a database level lock.
> Details: (Mode:Shared, Status:GRANT, Owner:SESSION)
> What causes these db level locks?
> All are SHARED level locks - and the status for is GRANT - meaning that
> the
> lock has been successfully obtained.
> How can I prevent these locks from appearing?
> Cheers!
> SQLCatZ
>
>|||Hello Mike,
If the connection is established only to check on the status of the db -
then it should be a for a very short duration. The entry for this kind of
lock should appear and then disappear from the 'Current Activity :
Locks/Object' view. It should not persist for hours on end.
What should I look out for in the design/code that is creating these
multiple db level locks to persist for extended periods of time.
Thank you.
SQLCatZ|||Hi
The client connection, as long as it is open, holds the DB Shared Lock (SQL
Server does it on it's behalf).
There is nothing wrong with those locks, and at most, there could be 32'737
of them if all connections are used that SQL Server supports.
It is a small number compared to the locks required my most SELECT and
UPDATE operations.
The application should also be looked at. The principle of "Acquire Late,
Release Early" should always be applied. An application should not open a
connection and hold it open until it gets closed, rather, it should open the
connection, do it's query, and close the connection again. Connection
pooling will negate any performance problems related to establishing and
tearing down connections in short intervals.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"SQLCatz" <SQLCatz@.discussions.microsoft.com> wrote in message
news:98FF664C-472A-4A50-B383-719E37F10BFE@.microsoft.com...
> Hello Mike,
> If the connection is established only to check on the status of the db -
> then it should be a for a very short duration. The entry for this kind of
> lock should appear and then disappear from the 'Current Activity :
> Locks/Object' view. It should not persist for hours on end.
> What should I look out for in the design/code that is creating these
> multiple db level locks to persist for extended periods of time.
> Thank you.
> SQLCatZ
>
>|||Hello Mike,
Thank you!
Your reply cleared things up.
I was aware of the "Acquire Late, Release Early" principle - but felt very
very uncomfortable when I saw a bunch of DB level locks hanging around for
extended periods of time.
Cheers!
SQLCatZ|||Basically Db locks prevent anyone from doing RESTORE and DROP DATABASE. When
MS looked into how
prohibiting these actions for 7.0, they realized that using the already pres
ent lock handing would
be a clean way of doing it.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"SQLCatz" <SQLCatz@.discussions.microsoft.com> wrote in message
news:27C4CB52-A0FE-496F-B41D-86955F2012DB@.microsoft.com...
> Hello Mike,
> Thank you!
> Your reply cleared things up.
> I was aware of the "Acquire Late, Release Early" principle - but felt very
> very uncomfortable when I saw a bunch of DB level locks hanging around for
> extended periods of time.
> Cheers!
> SQLCatZ
>
Showing posts with label lock. Show all posts
Showing posts with label lock. Show all posts
Monday, March 19, 2012
Current Activity : Locks/Object : DB Level locks.
The Current Activity monitor (Locks/Object) shows a number of processes that
have obtained a database level lock.
Details: (Mode:Shared, Status:GRANT, Owner:SESSION)
What causes these db level locks?
All are SHARED level locks - and the status for is GRANT - meaning that the
lock has been successfully obtained.
How can I prevent these locks from appearing?
Cheers!
SQLCatZ
Hi
Every connection will acquire a DB level lock when the connection is
established, to the database specified in the connection string from the
client..
This enables SQL Server to do a very fast lookup to see if a DB is in use
(for auto close, detach, drop database etc).
It is a shared lock.
You can not set this.
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"SQLCatz" <SQLCatz@.discussions.microsoft.com> wrote in message
news:DA43536F-E52A-4D1A-AB83-6433FEA6D1BD@.microsoft.com...
> The Current Activity monitor (Locks/Object) shows a number of processes
> that
> have obtained a database level lock.
> Details: (Mode:Shared, Status:GRANT, Owner:SESSION)
> What causes these db level locks?
> All are SHARED level locks - and the status for is GRANT - meaning that
> the
> lock has been successfully obtained.
> How can I prevent these locks from appearing?
> Cheers!
> SQLCatZ
>
>
|||Hello Mike,
If the connection is established only to check on the status of the db -
then it should be a for a very short duration. The entry for this kind of
lock should appear and then disappear from the 'Current Activity :
Locks/Object' view. It should not persist for hours on end.
What should I look out for in the design/code that is creating these
multiple db level locks to persist for extended periods of time.
Thank you.
SQLCatZ
|||Hi
The client connection, as long as it is open, holds the DB Shared Lock (SQL
Server does it on it's behalf).
There is nothing wrong with those locks, and at most, there could be 32'737
of them if all connections are used that SQL Server supports.
It is a small number compared to the locks required my most SELECT and
UPDATE operations.
The application should also be looked at. The principle of "Acquire Late,
Release Early" should always be applied. An application should not open a
connection and hold it open until it gets closed, rather, it should open the
connection, do it's query, and close the connection again. Connection
pooling will negate any performance problems related to establishing and
tearing down connections in short intervals.
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"SQLCatz" <SQLCatz@.discussions.microsoft.com> wrote in message
news:98FF664C-472A-4A50-B383-719E37F10BFE@.microsoft.com...
> Hello Mike,
> If the connection is established only to check on the status of the db -
> then it should be a for a very short duration. The entry for this kind of
> lock should appear and then disappear from the 'Current Activity :
> Locks/Object' view. It should not persist for hours on end.
> What should I look out for in the design/code that is creating these
> multiple db level locks to persist for extended periods of time.
> Thank you.
> SQLCatZ
>
>
|||Hello Mike,
Thank you!
Your reply cleared things up.
I was aware of the "Acquire Late, Release Early" principle - but felt very
very uncomfortable when I saw a bunch of DB level locks hanging around for
extended periods of time.
Cheers!
SQLCatZ
|||Basically Db locks prevent anyone from doing RESTORE and DROP DATABASE. When MS looked into how
prohibiting these actions for 7.0, they realized that using the already present lock handing would
be a clean way of doing it.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"SQLCatz" <SQLCatz@.discussions.microsoft.com> wrote in message
news:27C4CB52-A0FE-496F-B41D-86955F2012DB@.microsoft.com...
> Hello Mike,
> Thank you!
> Your reply cleared things up.
> I was aware of the "Acquire Late, Release Early" principle - but felt very
> very uncomfortable when I saw a bunch of DB level locks hanging around for
> extended periods of time.
> Cheers!
> SQLCatZ
>
have obtained a database level lock.
Details: (Mode:Shared, Status:GRANT, Owner:SESSION)
What causes these db level locks?
All are SHARED level locks - and the status for is GRANT - meaning that the
lock has been successfully obtained.
How can I prevent these locks from appearing?
Cheers!
SQLCatZ
Hi
Every connection will acquire a DB level lock when the connection is
established, to the database specified in the connection string from the
client..
This enables SQL Server to do a very fast lookup to see if a DB is in use
(for auto close, detach, drop database etc).
It is a shared lock.
You can not set this.
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"SQLCatz" <SQLCatz@.discussions.microsoft.com> wrote in message
news:DA43536F-E52A-4D1A-AB83-6433FEA6D1BD@.microsoft.com...
> The Current Activity monitor (Locks/Object) shows a number of processes
> that
> have obtained a database level lock.
> Details: (Mode:Shared, Status:GRANT, Owner:SESSION)
> What causes these db level locks?
> All are SHARED level locks - and the status for is GRANT - meaning that
> the
> lock has been successfully obtained.
> How can I prevent these locks from appearing?
> Cheers!
> SQLCatZ
>
>
|||Hello Mike,
If the connection is established only to check on the status of the db -
then it should be a for a very short duration. The entry for this kind of
lock should appear and then disappear from the 'Current Activity :
Locks/Object' view. It should not persist for hours on end.
What should I look out for in the design/code that is creating these
multiple db level locks to persist for extended periods of time.
Thank you.
SQLCatZ
|||Hi
The client connection, as long as it is open, holds the DB Shared Lock (SQL
Server does it on it's behalf).
There is nothing wrong with those locks, and at most, there could be 32'737
of them if all connections are used that SQL Server supports.
It is a small number compared to the locks required my most SELECT and
UPDATE operations.
The application should also be looked at. The principle of "Acquire Late,
Release Early" should always be applied. An application should not open a
connection and hold it open until it gets closed, rather, it should open the
connection, do it's query, and close the connection again. Connection
pooling will negate any performance problems related to establishing and
tearing down connections in short intervals.
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"SQLCatz" <SQLCatz@.discussions.microsoft.com> wrote in message
news:98FF664C-472A-4A50-B383-719E37F10BFE@.microsoft.com...
> Hello Mike,
> If the connection is established only to check on the status of the db -
> then it should be a for a very short duration. The entry for this kind of
> lock should appear and then disappear from the 'Current Activity :
> Locks/Object' view. It should not persist for hours on end.
> What should I look out for in the design/code that is creating these
> multiple db level locks to persist for extended periods of time.
> Thank you.
> SQLCatZ
>
>
|||Hello Mike,
Thank you!
Your reply cleared things up.
I was aware of the "Acquire Late, Release Early" principle - but felt very
very uncomfortable when I saw a bunch of DB level locks hanging around for
extended periods of time.
Cheers!
SQLCatZ
|||Basically Db locks prevent anyone from doing RESTORE and DROP DATABASE. When MS looked into how
prohibiting these actions for 7.0, they realized that using the already present lock handing would
be a clean way of doing it.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"SQLCatz" <SQLCatz@.discussions.microsoft.com> wrote in message
news:27C4CB52-A0FE-496F-B41D-86955F2012DB@.microsoft.com...
> Hello Mike,
> Thank you!
> Your reply cleared things up.
> I was aware of the "Acquire Late, Release Early" principle - but felt very
> very uncomfortable when I saw a bunch of DB level locks hanging around for
> extended periods of time.
> Cheers!
> SQLCatZ
>
Current Activity : Locks/Object : DB Level locks.
The Current Activity monitor (Locks/Object) shows a number of processes that
have obtained a database level lock.
Details: (Mode:Shared, Status:GRANT, Owner:SESSION)
What causes these db level locks?
All are SHARED level locks - and the status for is GRANT - meaning that the
lock has been successfully obtained.
How can I prevent these locks from appearing?
Cheers!
SQLCatZHi
Every connection will acquire a DB level lock when the connection is
established, to the database specified in the connection string from the
client..
This enables SQL Server to do a very fast lookup to see if a DB is in use
(for auto close, detach, drop database etc).
It is a shared lock.
You can not set this.
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"SQLCatz" <SQLCatz@.discussions.microsoft.com> wrote in message
news:DA43536F-E52A-4D1A-AB83-6433FEA6D1BD@.microsoft.com...
> The Current Activity monitor (Locks/Object) shows a number of processes
> that
> have obtained a database level lock.
> Details: (Mode:Shared, Status:GRANT, Owner:SESSION)
> What causes these db level locks?
> All are SHARED level locks - and the status for is GRANT - meaning that
> the
> lock has been successfully obtained.
> How can I prevent these locks from appearing?
> Cheers!
> SQLCatZ
>
>|||Hello Mike,
If the connection is established only to check on the status of the db -
then it should be a for a very short duration. The entry for this kind of
lock should appear and then disappear from the 'Current Activity :
Locks/Object' view. It should not persist for hours on end.
What should I look out for in the design/code that is creating these
multiple db level locks to persist for extended periods of time.
Thank you.
SQLCatZ|||Hi
The client connection, as long as it is open, holds the DB Shared Lock (SQL
Server does it on it's behalf).
There is nothing wrong with those locks, and at most, there could be 32'737
of them if all connections are used that SQL Server supports.
It is a small number compared to the locks required my most SELECT and
UPDATE operations.
The application should also be looked at. The principle of "Acquire Late,
Release Early" should always be applied. An application should not open a
connection and hold it open until it gets closed, rather, it should open the
connection, do it's query, and close the connection again. Connection
pooling will negate any performance problems related to establishing and
tearing down connections in short intervals.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"SQLCatz" <SQLCatz@.discussions.microsoft.com> wrote in message
news:98FF664C-472A-4A50-B383-719E37F10BFE@.microsoft.com...
> Hello Mike,
> If the connection is established only to check on the status of the db -
> then it should be a for a very short duration. The entry for this kind of
> lock should appear and then disappear from the 'Current Activity :
> Locks/Object' view. It should not persist for hours on end.
> What should I look out for in the design/code that is creating these
> multiple db level locks to persist for extended periods of time.
> Thank you.
> SQLCatZ
>
>|||Hello Mike,
Thank you!
Your reply cleared things up.
I was aware of the "Acquire Late, Release Early" principle - but felt very
very uncomfortable when I saw a bunch of DB level locks hanging around for
extended periods of time.
Cheers!
SQLCatZ|||Basically Db locks prevent anyone from doing RESTORE and DROP DATABASE. When MS looked into how
prohibiting these actions for 7.0, they realized that using the already present lock handing would
be a clean way of doing it.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"SQLCatz" <SQLCatz@.discussions.microsoft.com> wrote in message
news:27C4CB52-A0FE-496F-B41D-86955F2012DB@.microsoft.com...
> Hello Mike,
> Thank you!
> Your reply cleared things up.
> I was aware of the "Acquire Late, Release Early" principle - but felt very
> very uncomfortable when I saw a bunch of DB level locks hanging around for
> extended periods of time.
> Cheers!
> SQLCatZ
>
have obtained a database level lock.
Details: (Mode:Shared, Status:GRANT, Owner:SESSION)
What causes these db level locks?
All are SHARED level locks - and the status for is GRANT - meaning that the
lock has been successfully obtained.
How can I prevent these locks from appearing?
Cheers!
SQLCatZHi
Every connection will acquire a DB level lock when the connection is
established, to the database specified in the connection string from the
client..
This enables SQL Server to do a very fast lookup to see if a DB is in use
(for auto close, detach, drop database etc).
It is a shared lock.
You can not set this.
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"SQLCatz" <SQLCatz@.discussions.microsoft.com> wrote in message
news:DA43536F-E52A-4D1A-AB83-6433FEA6D1BD@.microsoft.com...
> The Current Activity monitor (Locks/Object) shows a number of processes
> that
> have obtained a database level lock.
> Details: (Mode:Shared, Status:GRANT, Owner:SESSION)
> What causes these db level locks?
> All are SHARED level locks - and the status for is GRANT - meaning that
> the
> lock has been successfully obtained.
> How can I prevent these locks from appearing?
> Cheers!
> SQLCatZ
>
>|||Hello Mike,
If the connection is established only to check on the status of the db -
then it should be a for a very short duration. The entry for this kind of
lock should appear and then disappear from the 'Current Activity :
Locks/Object' view. It should not persist for hours on end.
What should I look out for in the design/code that is creating these
multiple db level locks to persist for extended periods of time.
Thank you.
SQLCatZ|||Hi
The client connection, as long as it is open, holds the DB Shared Lock (SQL
Server does it on it's behalf).
There is nothing wrong with those locks, and at most, there could be 32'737
of them if all connections are used that SQL Server supports.
It is a small number compared to the locks required my most SELECT and
UPDATE operations.
The application should also be looked at. The principle of "Acquire Late,
Release Early" should always be applied. An application should not open a
connection and hold it open until it gets closed, rather, it should open the
connection, do it's query, and close the connection again. Connection
pooling will negate any performance problems related to establishing and
tearing down connections in short intervals.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"SQLCatz" <SQLCatz@.discussions.microsoft.com> wrote in message
news:98FF664C-472A-4A50-B383-719E37F10BFE@.microsoft.com...
> Hello Mike,
> If the connection is established only to check on the status of the db -
> then it should be a for a very short duration. The entry for this kind of
> lock should appear and then disappear from the 'Current Activity :
> Locks/Object' view. It should not persist for hours on end.
> What should I look out for in the design/code that is creating these
> multiple db level locks to persist for extended periods of time.
> Thank you.
> SQLCatZ
>
>|||Hello Mike,
Thank you!
Your reply cleared things up.
I was aware of the "Acquire Late, Release Early" principle - but felt very
very uncomfortable when I saw a bunch of DB level locks hanging around for
extended periods of time.
Cheers!
SQLCatZ|||Basically Db locks prevent anyone from doing RESTORE and DROP DATABASE. When MS looked into how
prohibiting these actions for 7.0, they realized that using the already present lock handing would
be a clean way of doing it.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"SQLCatz" <SQLCatz@.discussions.microsoft.com> wrote in message
news:27C4CB52-A0FE-496F-B41D-86955F2012DB@.microsoft.com...
> Hello Mike,
> Thank you!
> Your reply cleared things up.
> I was aware of the "Acquire Late, Release Early" principle - but felt very
> very uncomfortable when I saw a bunch of DB level locks hanging around for
> extended periods of time.
> Cheers!
> SQLCatZ
>
Wednesday, March 7, 2012
Cube Processing Error
Hello,
I'm getting an error while processing some cubes. "Cannot lock object
'lazy processing token'. Error: Connection to the server is lost." This
only occurs while processing certain cubes on the 64-bit version of AS
and occurs just after the 'Writing Aggregations and indexes' portion of
processing.
Any recommendations or ideas would be appreciated.
-Justin
Justin,
I am having the same problem. Let me know if you were able to find a solution.
Luke
Originally posted by Justin
Hello,
I'm getting an error while processing some cubes. "Cannot lock object
'lazy processing token'. Error: Connection to the server is lost." This
only occurs while processing certain cubes on the 64-bit version of AS
and occurs just after the 'Writing Aggregations and indexes' portion of
processing.
Any recommendations or ideas would be appreciated.
-Justin Originally posted by Justin
Hello,
We also have same problem. "Cannot lock object
'lazy processing token'. Error: Connection to the server is lost." We are using separate servers, one for sql (64-bit MS SQL server on Win2003-Microsoft SQL Server 2000 - 8.00.760 (Intel IA-64)
Feb 6 2003 16:07:24
Copyright (c) 1988-2003 Microsoft Corporation
Enterprise Edition (64-bit) on Windows NT 5.2 (Build 3790: ) and another for AS server (64-bit AS on Win2003-Microsoft SQL Server Analysis Services
Microsoft Corporation Version: 8.00.382). And we are processing the cubes from 64-bit AS. Some of the cubes fail (not all of them). When we process the cubes from another 32-bit AS it doesn't fail. Do you have the similar system configuration?
When we redesigned the cube, which failing on 64-bit AS, did not fail..
-Selma
The cube processing only fails at the last second, after it has been giving status for the entire process. My guess is there are two connections opened; one is used to show status messages and is active throughout the process; not sure what the other one is used for, but it is inactive until the commit, which explains why our firewall is dropping the connection - and then the commit fails.
Luke
Originally posted by Selma
I'm getting an error while processing some cubes. "Cannot lock object
'lazy processing token'. Error: Connection to the server is lost." This
only occurs while processing certain cubes on the 64-bit version of AS
and occurs just after the 'Writing Aggregations and indexes' portion of
processing.
Any recommendations or ideas would be appreciated.
-Justin
Justin,
I am having the same problem. Let me know if you were able to find a solution.
Luke
Quote:
Hello,
I'm getting an error while processing some cubes. "Cannot lock object
'lazy processing token'. Error: Connection to the server is lost." This
only occurs while processing certain cubes on the 64-bit version of AS
and occurs just after the 'Writing Aggregations and indexes' portion of
processing.
Any recommendations or ideas would be appreciated.
-Justin
Quote:
Hello,
We also have same problem. "Cannot lock object
'lazy processing token'. Error: Connection to the server is lost." We are using separate servers, one for sql (64-bit MS SQL server on Win2003-Microsoft SQL Server 2000 - 8.00.760 (Intel IA-64)
Feb 6 2003 16:07:24
Copyright (c) 1988-2003 Microsoft Corporation
Enterprise Edition (64-bit) on Windows NT 5.2 (Build 3790: ) and another for AS server (64-bit AS on Win2003-Microsoft SQL Server Analysis Services
Microsoft Corporation Version: 8.00.382). And we are processing the cubes from 64-bit AS. Some of the cubes fail (not all of them). When we process the cubes from another 32-bit AS it doesn't fail. Do you have the similar system configuration?
When we redesigned the cube, which failing on 64-bit AS, did not fail..
-Selma
The cube processing only fails at the last second, after it has been giving status for the entire process. My guess is there are two connections opened; one is used to show status messages and is active throughout the process; not sure what the other one is used for, but it is inactive until the commit, which explains why our firewall is dropping the connection - and then the commit fails.
Luke
Quote:
Subscribe to:
Posts (Atom)