We are currently having a problem with our SQL server. What seems to have
happened is a lock has been put on a row and then hasnt been removed, so now
we can't update because the lock is still there.
I'm wondering what we can do about this. Is there anyway you can delete or
remove locks in SQL Server?
At the moment, using SQL Sever, I can see a list of locks on the database
and table in question. What happens if I delete them? Each lock is
associated with a process - what happens if I kill the process?
Thanks to anyone who can offer any advice on this
kindest regards
SimonHi,
Before killing the process, try to identify the issue.
I feel that u are not commiting the transaction. after a Begin TRAN.
Thanks
Hari
MCDBA
"Simon Harvey" <simon.harvey@.the-web-works.co.uk> wrote in message
news:uXeVMVsAEHA.3400@.tk2msftngp13.phx.gbl...
> Hi everyone,
> We are currently having a problem with our SQL server. What seems to have
> happened is a lock has been put on a row and then hasnt been removed, so
now
> we can't update because the lock is still there.
> I'm wondering what we can do about this. Is there anyway you can delete or
> remove locks in SQL Server?
> At the moment, using SQL Sever, I can see a list of locks on the database
> and table in question. What happens if I delete them? Each lock is
> associated with a process - what happens if I kill the process?
> Thanks to anyone who can offer any advice on this
> kindest regards
> Simon
>|||This is a multi-part message in MIME format.
--=_NextPart_000_0034_01C40295.3C0B6960
Content-Type: text/plain;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
Killing the process that holds the locks will rollback the transaction =of that process, thereby releasing all of its locks. However, to save =yourself this kind of trouble in the future, you should invest some time =into troubleshooting the problem. There are probably a couple of =options, but all of them come down to why a transaction has not =committed.
See if224453 INF: Understanding and Resolving SQL Server 7.0 or 2000 =Blocking Problems
(http://support.microsoft.com/?id=3D224453) helps you out any. 171224 =INF: Understanding How the Transact-SQL KILL Command Works =(http://support.microsoft.com/?id=3D171224) might also be interesting =for you.
Thanks,
Ryan Stonecipher
Microsoft SQL Server Storage Engine
"Simon Harvey" <simon.harvey@.the-web-works.co.uk> wrote in message =news:uXeVMVsAEHA.3400@.tk2msftngp13.phx.gbl...
Hi everyone,
We are currently having a problem with our SQL server. What seems to =have
happened is a lock has been put on a row and then hasnt been removed, =so now
we can't update because the lock is still there.
I'm wondering what we can do about this. Is there anyway you can =delete or
remove locks in SQL Server?
At the moment, using SQL Sever, I can see a list of locks on the =database
and table in question. What happens if I delete them? Each lock is
associated with a process - what happens if I kill the process?
Thanks to anyone who can offer any advice on this
kindest regards
Simon
--=_NextPart_000_0034_01C40295.3C0B6960
Content-Type: text/html;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
Killing the process that holds the locks will =rollback the transaction of that process, thereby releasing all of its locks. =However, to save yourself this kind of trouble in the future, you should invest =some time into troubleshooting the problem. There are probably a couple of =options, but all of them come down to why a transaction has not =committed.
See if224453 INF: Understanding and Resolving SQL =Server 7.0 or 2000 Blocking Problems(http://support.microsoft.com/?id=3D224453">http://support.microso=ft.com/?id=3D224453) helps you out any. 171224 INF: Understanding How the Transact-SQL KILL =Command Works (http://support.microsoft.com/?id=3D171224">http://support.microso=ft.com/?id=3D171224) might also be interesting for you.
Thanks,
Ryan Stonecipher
Microsoft SQL Server Storage Engine
"Simon Harvey"
--=_NextPart_000_0034_01C40295.3C0B6960--
No comments:
Post a Comment