查找和停止正在执行作业/死锁

【查找作业：】

执行存储过程查看所有作业状态：

execute master.dbo.xp_sqlagent_enum_jobs 1, 'sa'

 

若执行结果中state字段为1的作业即表示正在运行（为4则表示执行完毕，无论成功与否）

 

 

-----------------------------------------------------

【停止作业：】

 

 

========================================================================================

 

【查找死锁：】

CREATE Table #Who(spid int,    ecid int,    status nvarchar(50),    loginname nvarchar(50),    hostname nvarchar(50),    blk int,    dbname nvarchar(50),    cmd nvarchar(50),    request_ID int);CREATE Table #Lock(spid int,    dpid int,    objid int,    indld int,    [Type] nvarchar(20),    Resource nvarchar(50),    Mode nvarchar(10),    Status nvarchar(10));INSERT INTO #Who    EXEC sp_who active  --看哪个引起的阻塞，blk INSERT INTO #Lock    EXEC sp_lock  --看锁住了那个资源id，objid DECLARE @DBName nvarchar(20);SET @DBName='NameOfDataBase'SELECT #Who.* FROM #Who WHERE dbname=@DBNameSELECT #Lock.* FROM #Lock    JOIN #Who        ON #Who.spid=#Lock.spid            AND dbname=@DBName;--最后发送到SQL Server的语句DECLARE crsr Cursor FOR    SELECT blk FROM #Who WHERE dbname=@DBName AND blk<>0;DECLARE @blk int;open crsr;FETCH NEXT FROM crsr INTO @blk;WHILE (@@FETCH_STATUS = 0)BEGIN;    dbcc inputbuffer(@blk);    FETCH NEXT FROM crsr INTO @blk;END;close crsr;DEALLOCATE crsr;--锁定的资源SELECT #Who.spid,hostname,objid,[type],mode,object_name(objid) as objName FROM #Lock    JOIN #Who        ON #Who.spid=#Lock.spid            AND dbname=@DBName    WHERE objid<>0;DROP Table #Who;DROP Table #Lock;

 

【停止死锁：】

依照上面执行结果查看那个spid处于wait状态，然后用kill spid来干掉