Thanks Franz and David,
I'll let you know what I find.
P
-----Original Message-----
From: Franz Zoister
[mailto:mssqldba-ezmlmshield-x74914743.[Email address protected]
Sent: Thursday, May 27, 2004 11:03 AM
To: LazyDBA.com Discussion
Subject: RE: SQL Server not releasing locks
Hi!
Maybe this thoughts would help :
These locks must go away if the transaction is committed or rolled back. The same with the service pack applied because the escalacion-lock-bug-fix addressed the problem that locks where held to the end of the transaction instead correctly to the end of the statement.
When there are locks there must be a transaction. (this holds even for shared locks in read commited mode which only live as long as the statement even if you dont started a transaction, because a single statement is implicitely running in a transaction )
It is possible that you did the following in an application:
1. Open a connection
2. BeginTransaction ( in ADO objConn.BeginTran() )
3. Executed a statement which for example updates some data on this connection( locks must then be held until the end of the transaction )
for example with (in ADO: objConn.Execute (update theField from theData where theRow = 'rowValue')
4. ..... do anything but do not one of the 3 things:
*) Commit or Rollback the transaction
*) Close the connection
*) Termiminate the process which opened the connection
Then you woul see what you see now.
If you have the process id then it is possible to see if there open transactions on that process with the query (there are other ways too) :
select open_tran, *
from master.dbo.sysprocesses
where spid = <theProcessID>
In your you application you have a connection, in Sql server this connection is assigned to a process so the process id corresponds with your connection.
best regards
FZ
-----Original Message-----
From: Lee David
[mailto:mssqldba-ezmlmshield-x8573214.[Email address protected]
Sent: Donnerstag, 27. Mai 2004 16:29
To: LazyDBA.com Discussion
Subject: RE: SQL Server not releasing locks
Paul,
There is a known issue with lock escalation in SP2. SQL server will not
always release the shared lock if it needs to escalate it.
http://support.microsoft.com/default.aspx?scid=kb;en-us;306908
Use the link above and then select the link half way down the page 'ALL
FIXES in SP3'; search that page for 'escalation' and you should find it.
We have also had ODBC timeout issues where the connection has timed out
during a transaction which can leave locks lying about.
Hope that helps
David Lee
Database Administrator
Carlson Marketing Group
-----Original Message-----
From: Schlieper Paul
[mailto:mssqldba-ezmlmshield-x53416424.[Email address protected]
Sent: Thursday, May 27, 2004 2:44 PM
To: LazyDBA.com Discussion
Subject: SQL Server not releasing locks
Hi all,
Using SQL2K SP2 on Win2K SP4.
Occasionally, we see a situation where SQL Server does not release locks
held by one connection, and which blocks other connections.
Using sp_who2, I can determine that the spid at the head of the blocking
chain is "Sleeping", Command is "Awaiting command". Speaking to the user
whose spid is at the head of the chain, they claim that everything looks
normal from their end.
We used to see quite a bit of this in the days of SQL 6.5, but very rare
since moving to SQL 2K.
Does anyone have any ideas what could be the cause, or how to go about
tracking it down?
P
---------------------------------------------------------------------
TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY Website :
http://www.LazyDBA.com To unsubscribe:
http://www.lazydba.com/unsubscribe.html
For additional commands, e-mail: mssqldba-[Email address protected]
This message and any attachment are intended for use only by the individual to whom they are specifically addressed above, and may contain privileged or
confidential information. Any unauthorized dissemination or copying of this message or any attachment, and any use or disclosure of any information contained in
them, is strictly prohibited and may be illegal. If you are not the intended recipient of this message, please notify the sender immediately and delete it from any
computer.
Any views expressed in this message are those of the individual sender, except where the sender specifically states them to be the views of the company
---------------------------------------------------------------------
TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
Website : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html
For additional commands, e-mail: mssqldba-[Email address protected]
---------------------------------------------------------------------
TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
Website : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html
For additional commands, e-mail: mssqldba-[Email address protected]
MS Sql Server LazyDBA home page