SqlServer定时备份数据库和定时杀死数据库死锁解决

--自动备份并保存最近5天的SQL数据库作业脚本
DECLARE @filename VARCHAR(255)
DECLARE @date DATETIME
SELECT @date=GETDATE()
SELECT @filename = 'G:\存放位置\数据库名称-'+CAST(DATEPART(yyyy,@date) as varchar)+'-'+CAST(DATEPART(mm,@date) as varchar)+'-'+CAST(DATEPART(dd,@date) as varchar)+'.bak'
BACKUP DATABASE [数据库名称] TO  DISK = @filename WITH INIT
GO

DECLARE @OLDDATE DATETIME
SELECT @OLDDATE=GETDATE()-5
EXECUTE master.dbo.xp_delete_file 0,N'G:\存放位置',N'bak',@olddate,1

--数据库死锁解决，结合作业（百度）实现定时清除数据库死锁进程，存储过程放在master数据库中
USE master
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE PROCEDURE sp_who_lock
AS
    BEGIN
        DECLARE @spid INT ,
            @bl INT ,
            @intTransactionCountOnEntry INT ,
            @intRowcount INT ,
            @intCountProperties INT ,
            @intCounter INT
        CREATE TABLE #tmp_lock_who
            (
              id INT IDENTITY(1, 1) ,
              spid SMALLINT ,
              bl SMALLINT
            )
         IF @@ERROR  0
            RETURN @@ERROR
        INSERT INTO #tmp_lock_who ( spid, bl )
                SELECT 0, blocked
                    FROM ( SELECT *
                            FROM sys.sysprocesses
                            WHERE blocked > 0
                         ) a
                    WHERE NOT EXISTS ( SELECT *
                                        FROM ( SELECT *
                                                FROM sys.sysprocesses
                                                WHERE blocked > 0
                                             ) b
                                        WHERE a.blocked = spid )
                UNION
                SELECT spid, blocked
                    FROM sys.sysprocesses
                    WHERE blocked > 0
        IF @@ERROR  0
            RETURN @@ERROR
       -- 找到临时表的记录数
        SELECT @intCountProperties = COUNT(*), @intCounter = 1
            FROM #tmp_lock_who
         IF @@ERROR  0
            RETURN @@ERROR
        IF @intCountProperties = 0
            SELECT N'现在没有阻塞和死锁信息' AS message
       -- 循环开始
        WHILE @intCounter 

<p>　　上面sql语句执行完成也就在master数据库生成了存储过程，调用的代码很简单</p>
<p>
</p><pre class="brush:php;toolbar:false">--执行
EXEC sp_who_lock
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO