Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

How to unlock the table Expand / Collapse
Author
Message
Posted Sunday, June 6, 2010 8:44 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, November 1, 2010 4:19 AM
Points: 39, Visits: 150
Here I have a table locked by another process, so I cannot access it. I try writing a T-SQL to find the lucked or blocked process id and kill it. But seems that the query result column [A.blocked ] do not show the ture situation(always return 0). In this way I cannot find the lucked spid.

select   B.name,   A.cmd,   A.spid,A.blocked   from   master..sysprocesses  A   
join master..sysdatabases B on A.dbid = B.dbid
where B.name = DB_NAME()

Any friends have better idea? Go a step further, is it possible to find the lucked table?
Thanks!
Post #933313
Posted Sunday, June 6, 2010 11:13 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 9:13 AM
Points: 40,609, Visits: 37,070
Don't use sysprocesses, it's deprecated, included only for backward comparability and will be removed in a future version.
Use sys.dm_exec_requests and/or sys_dm_tran_locks.

The way to 'unlock' a table is to kill the connection holding the lock, or wait for that connection to finish what it's doing and let SQL release the locks.



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #933331
Posted Monday, June 7, 2010 2:15 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, December 9, 2014 12:45 PM
Points: 365, Visits: 1,341
You can also check the "processes" section of the activity monitor. Right click on the server in the object explorer and choose "activity monitor".
Post #933363
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse