查看: 1223|回复: 0

[SQLServer] 数据库出现阻塞及时邮件预警提醒(上)

发表于 2017-5-14 09:20:56
背景 如果数据库出现大量阻塞,如不及时进行人工干预,可能会出现数据库hang,严重甚至影响用户业务正常运转。我们希望尽量在出现阻塞的时候及时邮件通知相关干系人去响应,避免出现大量用户报障之后相关处理人员才后知后觉的情况发生,或者有张表可以存储当时的阻塞情况,供我们后期优化分析使用。基于上述需求,我们基于数据库的Job来收集数据库的阻塞情况,并使用sp_sent_dbmail发送邮件; 测试环境 Microsoft SQL Server 2012 - 11.0.2100.60 (X64) Feb 10 2012 19:39:15 Copyright (c) Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 6.2 (Build 9200: )
提前准备事项 可以提前注册发送邮件的账号 例如: 邮箱账号: 密码:省略 SMTP服务器: smtp.163.com 端口:25 注意登录邮箱设置SMTP 实现流程

流程

实现步骤 第一步:创建用来存放信息的表 a.新建监控库【azure_monitor】以及存放阻塞记录的表【monitor_blocking】
  1. --创建监控库
  2. IF DB_ID('azure_monitor') IS NULL
  3. BEGIN
  4. CREATE DATABASE azure_monitor;
  5. END;
  6. GO
  7. ALTER DATABASE azure_monitor SET RECOVERY SIMPLE;
复制代码
  1. USE [azure_monitor] --存放阻塞信息的库名
  2. GO
  3. IF OBJECT_ID('monitor_blocking','U') IS NOT NULL
  4. DROP TABLE dbo.Monitor_blocking
  5. GO
  6. CREATE TABLE [dbo].[Monitor_blocking](
  7. [monitor_id] [INT] IDENTITY(1,1) NOT NULL,
  8. [lock type] [NVARCHAR](60) NOT NULL,
  9. [database] [NVARCHAR](128) NULL,
  10. [blk object] [BIGINT] NULL,
  11. [lock req] [NVARCHAR](60) NOT NULL,
  12. [waiter sid] [INT] NOT NULL,
  13. [wait time] [BIGINT] NULL,
  14. [waiter_batch] [NVARCHAR](MAX) NULL,
  15. [waiter_stmt] [NVARCHAR](MAX) NULL,
  16. [blocker sid] [SMALLINT] NULL,
  17. [blocker_stmt] [NVARCHAR](MAX) NULL,
  18. [time] [DATETIME] NOT NULL,
  19. [blocking_date] AS (CONVERT([VARCHAR](100),[time],(23))),
  20. [confirm_flag] [BIT] NULL,
  21. [confirm_user] [NVARCHAR](50) NULL,
  22. [confirm_date] [DATETIME] NULL,
  23. CONSTRAINT [PK_monitor_blocking] PRIMARY KEY CLUSTERED
  24. (
  25. [monitor_id] ASC
  26. )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
  27. ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
  28. GO
  29. ALTER TABLE [dbo].[Monitor_blocking] ADD CONSTRAINT [DF_Monitor_blocking_confirm_flag] DEFAULT ((0)) FOR [confirm_flag]
  30. GO
  31. ALTER TABLE [dbo].[Monitor_blocking] ADD CONSTRAINT [DF_Monitor_blocking_confirm_date] DEFAULT (GETDATE()) FOR [confirm_date]
  32. GO
复制代码

  

第二步:创建收集信息的存储过程 a.创建收集阻塞的存储过程
  1. USE azure_monitor;
  2. GO
  3. IF OBJECT_ID('Monitor_p_blocking', 'p') IS NOT NULL
  4. DROP PROCEDURE Monitor_p_blocking;
  5. GO
  6. ----监控库 sql server blocking 的存储过程例子
  7. /*=============================================
  8. -- Author: jil.wen
  9. -- Create date: 2016/11/16
  10. -- Description: 监控数据库上阻塞情况;
  11. -- demo : exec dbo.Monitor_p_blocking
  12. ============================================= */
  13. CREATE PROCEDURE Monitor_p_blocking
  14. AS
  15. BEGIN
  16. SET NOCOUNT ON;
  17. INSERT INTO [dbo].[Monitor_blocking]
  18. ( [lock type] ,
  19. [database] ,
  20. [blk object] ,
  21. [lock req] ,
  22. [waiter sid] ,
  23. [wait time] ,
  24. waiter_batch ,
  25. waiter_stmt ,
  26. [blocker sid] ,
  27. blocker_stmt ,
  28. [time]
  29. )
  30. SELECT t1.resource_type AS [lock type] ,
  31. DB_NAME(resource_database_id) AS [database] ,
  32. t1.resource_associated_entity_id AS [blk object] ,
  33. t1.request_mode AS [lock req] -- lock requested
  34. ,
  35. t1.request_session_id AS [waiter sid] -- spid of waiter
  36. ,
  37. t2.wait_duration_ms AS [wait time] ,
  38. ( SELECT text
  39. FROM sys.dm_exec_requests AS r WITH ( NOLOCK ) --- get sql for waiter
  40. CROSS APPLY sys.dm_exec_sql_text(r.sql_handle)
  41. WHERE r.session_id = t1.request_session_id
  42. ) AS waiter_batch ,
  43. ( SELECT SUBSTRING(qt.text,
  44. r.statement_start_offset / 2,
  45. ( CASE WHEN r.statement_end_offset = -1
  46. THEN LEN(CONVERT(NVARCHAR(MAX), qt.text))
  47. * 2
  48. ELSE r.statement_end_offset
  49. END - r.statement_start_offset )
  50. / 2 + 1)
  51. FROM sys.dm_exec_requests AS r WITH ( NOLOCK )
  52. CROSS APPLY sys.dm_exec_sql_text(r.sql_handle)
  53. AS qt
  54. WHERE r.session_id = t1.request_session_id
  55. ) AS waiter_stmt --- statement executing now
  56. ,
  57. t2.blocking_session_id AS [blocker sid] --- spid of blocker
  58. ,
  59. ( SELECT text
  60. FROM sys.sysprocesses AS p WITH ( NOLOCK ) --- get sql for blocker
  61. CROSS APPLY sys.dm_exec_sql_text(p.sql_handle)
  62. WHERE p.spid = t2.blocking_session_id
  63. ) AS blocker_stmt ,
  64. GETDATE() time
  65. FROM sys.dm_tran_locks AS t1 WITH ( NOLOCK ) ,
  66. sys.dm_os_waiting_tasks AS t2 WITH ( NOLOCK )
  67. WHERE t1.lock_owner_address = t2.resource_address;
  68. SET NOCOUNT OFF;
  69. END;
  70. GO
复制代码
第三步:配置邮件设置,并测试邮件发送是否正常
  1. --配置邮件设置
  2. --1. 启用database mail
  3. use master
  4. GO
  5. exec sp_configure 'show advanced options',1
  6. reconfigure
  7. exec sp_configure 'Database mail XPs',1
  8. reconfigure
  9. GO
  10. --2. 添加account
  11. exec msdb..sysmail_add_account_sp
  12. @account_name = 'monitor_blocking' -- mail account
  13. ,@email_address = '某某@163.com' -- sendmail address
  14. ,@display_name = 'monitor_blocking' -- sendusername
  15. ,@replyto_address = null
  16. ,@description = null
  17. ,@mailserver_name = 'smtp.163.com' -- SMTP Address
  18. ,@mailserver_type = 'SMTP' -- SMTP
  19. ,@port = 25 -- port
  20. ,@username = '某某@163.com' -- account
  21. ,@password = '替换成密码' -- pwd
  22. ,@use_default_credentials = 0
  23. ,@enable_ssl = 0 --is ssl enabled on SMTP server
  24. ,@account_id = NULL
  25. --3. 添加profile
  26. exec msdb..sysmail_add_profile_sp
  27. @profile_name = 'monitor_blocking' -- profile name
  28. ,@description = 'dba mail profile' -- profile description
  29. ,@profile_id = NULL
  30. --4. 关联account and profile
  31. exec msdb..sysmail_add_profileaccount_sp
  32. @profile_name = 'monitor_blocking' -- profile name
  33. ,@account_name = 'monitor_blocking' -- account name
  34. ,@sequence_number = 1 -- account order in profile
  35. --5. 测试验证发送database mail
  36. EXEC msdb.dbo.sp_send_dbmail
  37. @profile_name = 'monitor_blocking',
  38. @recipients = '某某@;某某@163.com;',
  39. @body = 'This is a testing mail',
  40. @subject = 'Testing Database Mail';
  41. GO
复制代码

测试邮件

后续步骤可以参考数据库出现阻塞及时邮件预警提醒(下)



回复

使用道具 举报