开发者社区> 杰克.陈> 正文
阿里云
为了无法计算的价值
打开APP
阿里云APP内打开

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

简介: 原文:SqlServer定时备份数据库和定时杀死数据库死锁解决 上周五组长更我说了一句要杀死数据库的死锁进程,因为自己对数据库不是很熟悉,突然组长说了我也就决定一定要倒腾一下,不然自己怎么提高呢?现在不研究,说不定下次还是要研究呢,倒腾出来了就可以在下次用到了,后来组长又补了一句:"还有定是备份数据库的问题要解决",说干就干。
+关注继续查看
原文:SqlServer定时备份数据库和定时杀死数据库死锁解决

上周五组长更我说了一句要杀死数据库的死锁进程,因为自己对数据库不是很熟悉,突然组长说了我也就决定一定要倒腾一下,不然自己怎么提高呢?现在不研究,说不定下次还是要研究呢,倒腾出来了就可以在下次用到了,后来组长又补了一句:"还有定是备份数据库的问题要解决",说干就干。

PS:Sqlserver 2008 R2,windows 8 64位

1.备份数据库

 因为要备份,我们就要用到Sqlserver的代理,默认数据库的代理是不开启的。需要我们手动开启的。

 

执行备份数据库脚本,现在将脚本公布,其实将这一段代码中需要保存的文件路径和数据库名称替换一下就可以实现备份了。但是还没有达到定时备份的目的 

--自动备份并保存最近5天的SQL数据库作业脚本 宋彪 20130310
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

  

  2.定时备份指定数据库

  刚刚开启了Sqlserver代理服务,其实我自己的理解就是一个定时器,不停的执行一些操作者分配给他的任务,有点像闹钟的感觉,看我的演示步骤

   第一步 

 

   第二步 

 

   第三步

 第四步

 第五步

 上面的步骤就是完成了定时备份指定数据库的功能!

 **************************************************************************

1.杀死数据库死锁进程

接下来介绍一些杀死数据库死锁进程的方法

下午折腾了半天找了很多的文章看了一下,发现很多都是使用到了master中的sys.sysprocesses表(http://msdn.microsoft.com/zh-cn/library/ms179881(SQL.90).aspx)这里附上msdn对这个表的解释,不懂的可以参考看看这里面的每个表字段的含义。

参考了网上的意见,大部分都是写一个存储过程在master数据库中,然后使用作业的方式定时杀死死锁进程的,觉得这个方法可行!

下面是存储过程SQL语句

--数据库死锁解决,结合作业(百度)实现定时清除数据库死锁进程,存储过程放在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

  上面sql语句执行完成也就在master数据库生成了存储过程,调用的代码很简单

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

 调用完成之后就可以杀死数据库当中的所有死锁进程

   题外话:如果判断那些进程是死锁进程,这个是关键点,在msdn中对sys.sysprocesses表的有一个字段blocked这个字段,去除所有大于0的数据库就是数据库的死锁进程数据库,然后使用KILL+SPID(进程ID)执行就可以杀死死锁进程,主要思想就是这些。

 2.定时杀死数据库死锁进程

 对于定时杀死数据库死锁进程,这里有两点需要注意

1.执行的顺序,现在master数据库创建存储过程,然后创建作业

2.作业的执行代码,执行代码是调用杀死死锁进程的存储过程

第一步

需要执行上面的杀死死锁进程的存储过程,只有master数据库中存在这个存储过程,才可以以作业的方式调用这个存储过程,否则就是调用失败!

 

第二步

创建一个新的作业,将刚刚的调用存储过程的SQL语句放入到新建作业=>步骤选项=>命令,中就可以了。最后一步执行计划自己根据实际情况进行设置  

关于Sqlserver数据库的两个难点也在这个下午被我自己吸收了。将来碰到类似的问题也可以轻松应对了,如果大家觉得有帮助可以帮小弟推荐一下哦!

如果文章中有错误的地方,大家可以联系我的qq:707055073

我的群:152652959

版权所有,违法必究。

转载请说明原文链接

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

相关文章
数据库必知词汇:SQL Server
SQL Server是美国微软(Microsoft)公司推出的关系型数据库管理系统,诞生之初是为了与Oracle和MySQL等关系型数据库管理系统进行竞争。
489 0
SQL SERVER学习2——数据库设计
数据库设计是数据库知识中比较重要的部分,我们需要了解数据库设计的基本步骤,E-R图的画法。 数据库设计的基本概述 检验一个数据库设计好坏的标准就是,看他是否能够方便的执行各种数据检索和处理操作,并且有利于数据的控制管理和维护。
950 0
现在的SQLSERVER数据库监控软件有哪些?
原文:现在的SQLSERVER数据库监控软件有哪些? 现在的SQLSERVER数据库监控软件有哪些? 收集了一下当前SQLSERVER数据库监控软件,发现开源免费的真的是“没有” Questsoftware  Quest's spotlight(收费)  http://www.
1221 0
SQL SERVER学习1——数据库概念
《SQL Server实例教程》(科学出版社) 数据库的基本概念 数据是载荷信息的物理符号,是数据库中存储的基本对象。 信息可以通过手势,眼神表达,但是表达信息的最佳方式还是数据。 数据有型和值,经过一定的解释,数据才会被理解,然后成为信息。
1002 0
SQL server数据库之触发器
SQL server数据库之触发器 在SQLserver数据库中触发器中大致分为两种:DDL、DML触发器,即系统触发器与表触发器,系统触发器是对数据库对象进行操作的触发器,表触发器是对表格的增删改时候所进行的触发器。
1150 0
SQL Server系统数据库备份最佳实践
原文: SQL Server系统数据库备份最佳实践 首先了解主要的系统数据库: 系统数据库 master 包含登录信息和其他数据库的核心信息 msdb 存储作业、操作员、警报、备份还原历史。
1719 0
2. SQL Server数据库状态监控 - 错误日志
原文:2. SQL Server数据库状态监控 - 错误日志 无论是操作系统 (Unix 或者Windows),还是应用程序 (Web 服务,数据库系统等等) ,通常都有自身的日志机制,以便故障时追溯现场及原因。
1133 0
3. SQL Server数据库状态监控 - 可用空间
原文:3. SQL Server数据库状态监控 - 可用空间 数据库用来存放数据,那么肯定需要存储空间,所以对磁盘空间的监视自然就很有必要了。 一. 磁盘可用空间 1. 操作系统命令或脚本、接口或工具 (1) DOS命令: fsutil volume diskfree C:\windows\...
947 0
+关注
杰克.陈
一个安静的程序猿~
10424
文章
2
问答
文章排行榜
最热
最新
相关电子书
更多
低代码开发师(初级)实战教程
立即下载
阿里巴巴DevOps 最佳实践手册
立即下载
冬季实战营第三期:MySQL数据库进阶实战
立即下载