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