mssql锁表解除

-- 查看锁状态
select spid,
       blocked, -- 如果为0或者空，基本上可以确定这条语句阻塞了其他语句，
       loginame,
       last_batch,
       status,
       cmd,
       hostname,
       program_name
  from sys.sysprocesses
 where spid in (select blocked from sys.sysprocesses where blocked <> 0)
    or (blocked <> 0)


-- 终止进程
 declare @spid  int  
Set @spid  = 83 --锁表进程 
declare @sql varchar(1000) 
set @sql='kill '+cast(@spid  as varchar) 
exec(@sql)

----------------------------------------------------------------------------------------
-- 另外的查看阻塞情况：
select A.SPID as 被阻塞进程,a.CMD AS 正在执行的操作,b.spid AS 阻塞进程号_主谋,b.cmd AS 阻塞进程正在执行的操作
from master..sysprocesses a,master..sysprocesses b
where a.blocked<>0 and a.blocked= b.spid

-- 可以根据spid查看正在执行的sql
dbcc inputbuffer(338);

-- 查看语句-正在执行

 

SELECT  [Spid] = session_id ,
            ecid ,
            [Database] = DB_NAME(sp.dbid) ,
            [User] = nt_username ,
            [Status] = er.status ,
            [Wait] = wait_type ,
            [Individual Query] = SUBSTRING(qt.text,
                                           er.statement_start_offset / 2,
                                           ( CASE WHEN er.statement_end_offset = -1
                                                  THEN LEN(CONVERT(NVARCHAR(MAX), qt.text))
                                                       * 2
                                                  ELSE er.statement_end_offset
                                             END - er.statement_start_offset )
                                           / 2) ,
            [Parent Query] = qt.text ,
            Program = program_name ,
            hostname ,
            nt_domain ,
            start_time
    FROM    sys.dm_exec_requests er
            INNER JOIN sys.sysprocesses sp ON er.session_id = sp.spid
            CROSS APPLY sys.dm_exec_sql_text(er.sql_handle) AS qt
    WHERE   session_id > 50 -- Ignore system spids.
            AND session_id NOT IN ( @@SPID ) -- Ignore this current statement.
ORDER BY    1 , 2

-- 查看语句执行过信息

 

 

select *
	 from sys.sysprocesses  
     where spid in (select blocked from sys.sysprocesses where blocked <> 0)  
        or (blocked <> 0)  ;	
SELECT TOP 1000 
       ST.text AS '执行的SQL语句',
       QS.execution_count AS '执行次数',
       QS.total_elapsed_time AS '耗时',
       QS.total_logical_reads AS '逻辑读取次数',
       QS.total_logical_writes AS '逻辑写入次数',
       QS.total_physical_reads AS '物理读取次数',       
       QS.creation_time AS '执行时间' ,  
       QS.*
FROM   sys.dm_exec_query_stats QS
       CROSS APPLY 
sys.dm_exec_sql_text(QS.sql_handle) ST
WHERE  QS.creation_time BETWEEN '2016-08-05 15:00:00' AND '2016-08-05 17:00:00' 
ORDER BY QS.total_elapsed_time DESC 

kill当前库所有进程

DECLARE @sql NVARCHAR(MAX),@currDbName NVARCHAR(100)
SET @currDbName='DB1'	--如果在当前库上执行，可改为: SET @currDbName = DB_NAME()
WHILE EXISTS( SELECT 1 FROM sys.sysprocesses WHERE dbid=DB_ID(@currDbName) AND spid!=@@SPID )
BEGIN
	SELECT TOP 1 @sql='kill '+CAST(spid AS NVARCHAR(50)) FROM sys.sysprocesses WHERE dbid=DB_ID(@currDbName) AND spid!=@@SPID  
	EXEC (@sql)	
END