如何在 SQL Server 2012 中解锁表？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/37156834/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to unlock table in SQL Server 2012?
提问by Pushkar Jaju
I created table in SQL Server 2012 and when I execute
我在 SQL Server 2012 和执行时创建了表
select * from tableName
it is taking a long time and some time returns no result.
这需要很长时间，有些时间没有返回任何结果。
Currently it has only 1 row. After searching I know it is being locked so please help how to unlock it or drop it?
目前它只有 1 行。搜索后我知道它被锁定了所以请帮助如何解锁或放下它？
回答by Pushkar Jaju
Thank you Guys.. It is resolved.
谢谢各位。。解决了。
I fired below query
我在下面的查询中解雇了
SELECT
OBJECT_NAME(P.object_id) AS TableName,
Resource_type,
request_session_id
FROM
sys.dm_tran_locks L
JOIN
sys.partitions P ON L.resource_associated_entity_id = p.hobt_id
WHERE
OBJECT_NAME(P.object_id) = 'P1Chronolog_IncidentActivityUpdates'
and killed that respective session by
并通过以下方式杀死了相应的会话
Kill session_ID
回答by dfortun
Get the SPID of what is locking the table and kill it, see below
获取锁定表的 SPID 并杀死它，见下文
SELECT r.start_time [Start Time],session_ID [SPID],
DB_NAME(database_id) [Database],
SUBSTRING(t.text,(r.statement_start_offset/2)+1,
CASE WHEN statement_end_offset=-1 OR statement_end_offset=0
THEN (DATALENGTH(t.Text)-r.statement_start_offset/2)+1
ELSE (r.statement_end_offset-r.statement_start_offset)/2+1
END) [Executing SQL],
Status,command,wait_type,wait_time,wait_resource,
last_wait_type
FROM sys.dm_exec_requests r
OUTER APPLY sys.dm_exec_sql_text(sql_handle) t
WHERE session_id != @@SPID -- don't show this query
AND session_id > 50 -- don't show system queries
ORDER BY r.start_time
DBCC opentran()
exec sp_who2 68
exec sp_lock 68
kill 68
