后天那篇小说计算一下怎样监察和控制SQL,大家来设置一下死锁(Dead Lock)告警吧

前些天那篇文章计算一下哪些监督SQL
Server的死锁,其实在此之前写过MS SQL
监察和控制错误日志的告警新闻
,这篇小说器重介绍怎么着监察和控制数据库的死锁,当然那篇小说不分析死锁发生的缘故、以及如何化解死锁。死锁(Dead
Lock)的错误消息在sys.messages中的message_id为1205,能够运用上面SQL查看。

今天这篇小说总计一下什么样监察和控制SQL
Server的死锁,其实在此从前写过MS SQL
监察和控制错误日志的告警新闻
,那篇文章注重介绍怎样监察和控制数据库的死锁,当然这篇文章不分析死锁产生的原由、以及怎么着消除死锁。死锁(Dead
Lock)的错误音讯在sys.messages中的message_id为1205,能够利用上面SQL查看。

 

 

 

 

  
SELECT
* FROM sys.messages WHERE message_id=1205

  
SELECT
* FROM sys.messages WHERE message_id=1205

 

 

 

 

那正是说接下去,大家来安装一下死锁(Dead Lock)告警吧,
如下所示,当然你能够采纳UI界面设置。

那么接下去,大家来安装一下死锁(Dead Lock)告警吧,
如下所示,当然你能够运用UI界面设置。

 

 

 

USE [msdb]

GO

 

IF NOT EXISTS(SELECT 1 FROM msdb.dbo.syscategories WHERE NAME='DBA_MONITORING' AND category_class=2)

BEGIN

 

EXEC msdb.dbo.sp_add_category

    @class=N'ALERT',

    @type=N'NONE',

    @name=N'DBA_MONITORING' ;

 

END

GO

 

IF EXISTS(SELECT 1 FROM msdb.dbo.sysalerts WHERE name='SQL Server Dead Lock Detected')

BEGIN

    EXEC msdb.dbo.sp_delete_alert @name=N'SQL Server Dead Lock Detected';

END

GO

 

 

IF NOT EXISTS(SELECT 1 FROM msdb.dbo.sysalerts WHERE name='SQL Server Dead Lock Detected')

BEGIN

EXEC msdb.dbo.sp_add_alert @name=N'SQL Server Dead Lock Detected', 

        @message_id=1205, 

        @severity=0, 

        @enabled=1, 

        @delay_between_responses=0, 

        @include_event_description_in=1, 

        @category_name=N'DBA_MONITORING', 

        @job_id=N'00000000-0000-0000-0000-000000000000'

END

GO

 

IF NOT EXISTS ( SELECT  *

                FROM    msdb.dbo.sysnotifications

                WHERE   alert_id = ( SELECT id

                                     FROM   msdb.dbo.sysalerts

                                     WHERE  name = 'SQL Server Dead Lock Detected'

                                   ) )

    BEGIN

 

        EXEC msdb.dbo.sp_add_notification @alert_name = N'SQL Server Dead Lock Detected',

            @operator_name = N'YourSQLDba_Operator', @notification_method = 1;

    END;

GO
 

USE [msdb]

GO

 

IF NOT EXISTS(SELECT 1 FROM msdb.dbo.syscategories WHERE NAME='DBA_MONITORING' AND category_class=2)

BEGIN

 

EXEC msdb.dbo.sp_add_category

    @class=N'ALERT',

    @type=N'NONE',

    @name=N'DBA_MONITORING' ;

 

END

GO

 

IF EXISTS(SELECT 1 FROM msdb.dbo.sysalerts WHERE name='SQL Server Dead Lock Detected')

BEGIN

    EXEC msdb.dbo.sp_delete_alert @name=N'SQL Server Dead Lock Detected';

END

GO

 

 

IF NOT EXISTS(SELECT 1 FROM msdb.dbo.sysalerts WHERE name='SQL Server Dead Lock Detected')

BEGIN

EXEC msdb.dbo.sp_add_alert @name=N'SQL Server Dead Lock Detected', 

        @message_id=1205, 

        @severity=0, 

        @enabled=1, 

        @delay_between_responses=0, 

        @include_event_description_in=1, 

        @category_name=N'DBA_MONITORING', 

        @job_id=N'00000000-0000-0000-0000-000000000000'

END

GO

 

IF NOT EXISTS ( SELECT  *

                FROM    msdb.dbo.sysnotifications

                WHERE   alert_id = ( SELECT id

                                     FROM   msdb.dbo.sysalerts

                                     WHERE  name = 'SQL Server Dead Lock Detected'

                                   ) )

    BEGIN

 

        EXEC msdb.dbo.sp_add_notification @alert_name = N'SQL Server Dead Lock Detected',

            @operator_name = N'YourSQLDba_Operator', @notification_method = 1;

    END;

GO

 

 

执行上面脚本后,就会在SQL
Server的告警里面新增八个名为SQL Server Dead Lock
Detected’的报告警方,那么以后是否OK了吧?当然不是,大家来测试证Bellamy下啊,首先准备测试的表和数据。

施行上边脚本后,就会在SQL
Server的报告警方里面新增2个名为SQL Server Dead Lock
Detected’的报告警方,那么今后是还是不是OK了吧?当然不是,大家来测试证美赞臣(Meadjohnson)(Nutrilon)下啊,首先准备测试的表和数据。

 

 

 

 

 

 

USE
YourSQLDba;

USE
YourSQLDba;

GO

GO

CREATE
TABLE DEADLOCK1(ID INT DEFAULT(0));

CREATE
TABLE DEADLOCK1(ID INT DEFAULT(0));

CREATE
TABLE DEADLOCK2(ID INT DEFAULT(0));

CREATE
TABLE DEADLOCK2(ID INT DEFAULT(0));

INSERT
INTO DEADLOCK1 VALUES(1);

INSERT
INTO DEADLOCK1 VALUES(1);

INSERT
INTO DEADLOCK2 VALUES(1);

INSERT
INTO DEADLOCK2 VALUES(1);

GO

GO

 

 

 

 

 

 

 

 

一般来说所示,在七个会话窗口进行上面脚本,构造死锁出现的风貌。

正如所示,在多个会话窗口实行上面脚本,构造死锁出现的现象。

 

 

 

 

–会话窗口1执行上边SQL

–会话窗口1执行上面SQL

BEGIN
TRAN

BEGIN
TRAN

  UPDATE DEADLOCK1 SET ID=ID+1;

  UPDATE DEADLOCK1 SET ID=ID+1;

  WAITFOR DELAY ’00:00:20′;

  WAITFOR DELAY ’00:00:20′;

  SELECT * FROM DEADLOCK2

  SELECT * FROM DEADLOCK2

ROLLBACK
TRAN;

ROLLBACK
TRAN;

 

 

 

 

EXEC
master..sp_altermessage 1205, ‘WITH_LOG’, TRUE;

EXEC
master..sp_altermessage 1205, ‘WITH_LOG’, TRUE;

GO

GO

 

 

 

 

 

 

–会话创造2执行下边SQL

–会话创设2实践上面SQL

 

 

BEGIN
TRAN

BEGIN
TRAN

  UPDATE DEADLOCK2 SET ID=ID+1;

  UPDATE DEADLOCK2 SET ID=ID+1;

  WAITFOR DELAY ’00:00:20′;

  WAITFOR DELAY ’00:00:20′;

  SELECT * FROM DEADLOCK1

  SELECT * FROM DEADLOCK1

 

 

ROLLBACK
TRAN;

ROLLBACK
TRAN;

 

 

 

 

 

 

正如截图所示,当死锁出现后,那么那一个告警设置是不是会发送邮件出来吗?
答案是或不是认的,你能够检查告警的野史场所,如下所示:

如下截图所示,当死锁出现后,那么那个告警设置是不是会发送邮件出来啊?
答案是不是定的,你能够检查告警的历史意况,如下所示:

 

 

 

 

图片 1

图片 2

 

 

 

 

 

 

从History界面,大家得以看看那些告警没有被触发,那么那一个是什么样来头吧?原因实在很简单,因为message_id为1205的新闻字段is_event_logged私下认可是0,那意味着出现谬误新闻将不会记入事件日志。我们能够运用徐熙娣(英文名:Elephant Dee)(英文名:Elephant Dee)QL将其值设置为1

从History界面,大家能够看到这些告警没有被触发,那么那一个是如何原因吗?原因其实很简单,因为message_id为1205的音讯字段is_event_logged暗中认可是0,那象征出现错误音信将不会记入事件日志。我们得以应用小SQL将其值设置为1

 

 

 

 

 

 

 

 

图片 3

图片 4

 

 

 

 

EXEC
master..sp_altermessage 1205, ‘WITH_LOG’, TRUE;

EXEC
master..sp_altermessage 1205, ‘WITH_LOG’, TRUE;

GO

GO

 

 

 

 

实行上边脚本后,message_id为1205的记录的is_event_logged字段值将棉被服装置为1,当数据库出现死锁时,就会被记录到不当日志,当然那么些只是简单音讯的记录,如若您要盯住、消除死锁难题,就供给记录死锁的详细音讯,需求在服务端针对富有的Session开启Trace flag 1222。

举办上边脚本后,message_id为1205的笔录的is_event_logged字段值将被设置为1,当数据库出现死锁时,就会被记录到错误日志,当然这么些只是简约音讯的笔录,假使你要跟踪、化解死锁难点,就供给记录死锁的详细音信,需求在服务端针对具有的Session开启Trace flag 1222。

 

 

DBCC
TRACEON(1222,-1);

DBCC
TRACEON(1222,-1);

 

 

 

 

图片 5

图片 6