我在 SQL Server 2012 中创建了一个表,在执行时
select * from tableName
查询时间很长,有时候甚至没有结果。
目前只有一行数据。查询后发现被锁住了,请帮忙解锁或删除它?
我在 SQL Server 2012 中创建了一个表,在执行时
select * from tableName
查询时间很长,有时候甚至没有结果。
目前只有一行数据。查询后发现被锁住了,请帮忙解锁或删除它?
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'
并杀死了该对应的会话
Kill session_ID
获取锁定表的进程(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
DBCC opentran()
exec sp_who2 68
exec sp_lock 68
kill 68
- Syed Nasir Abbas