Click here to monitor SSC
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in
Home       Members    Calendar    Who's On

Add to briefcase

How to unlock the table Expand / Collapse
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,   A.cmd,   A.spid,A.blocked   from   master..sysprocesses  A   
join master..sysdatabases B on A.dbid = B.dbid
where = DB_NAME()

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



Group: General Forum Members
Last Login: Today @ 5:18 AM
Points: 45,619, Visits: 44,147
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, MVP, M.Sc (Comp Sci)
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: Monday, August 8, 2016 1:50 AM
Points: 374, Visits: 1,454
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