June 6, 2010 at 8:44 pm
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!:-P
June 6, 2010 at 11:13 pm
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
June 7, 2010 at 2:15 am
You can also check the "processes" section of the activity monitor. Right click on the server in the object explorer and choose "activity monitor".
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply