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 <= @intCountProperties
            BEGIN
              -- 取第一条记录
                SELECT @spid = spid, @bl = bl
                    FROM #tmp_lock_who
                    WHERE Id = @intCounter
                BEGIN
                    IF @spid = 0
                        SELECT N'引起数据库死锁的是: ' + CAST(@bl AS VARCHAR(10))
                                + N'进程号,其执行的SQL语法如下'
                    ELSE
                        SELECT N'进程号SPID：' + CAST(@spid AS VARCHAR(10))
                                + N'被进程号SPID：' + CAST(@bl AS VARCHAR(10)) N'阻塞,其当前进程执行的SQL语法如下'
                    DBCC INPUTBUFFER (@bl )
                END
              -- 循环指针下移
                SET @intCounter = @intCounter + 1
            END
        DROP TABLE #tmp_lock_who
        RETURN 0
    END
go

--执行
EXEC sp_who_lock
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO