SQLserver数据库部署自动抓取慢日志并发送邮件

IT那活儿 2022-08-06

1576

点击上方“IT那活儿”公众号，关注后了解更多内容，不管IT什么活儿，干就完了！！！

安装邮件服务

1. 解压sendEmail-v156.zip得到两个文件：

sendEmail.exe

sendEmail.pl

2. 放置到C:\Windows\System32。

创建慢日志查询存储过程

1. 打开数据库->可编程性->存储过程->右键新建存储过程。

2. 选中存储过程右键，新建存储过程：

-- ================================================
-- Template generated from Template Explorer using:
-- Create Procedure (New Menu).SQL
--
-- Use the Specify Values for Template Parameters 
-- command (Ctrl-Shift-M) to fill in the parameter 
-- values below.
--
-- This block of comments will not be included in
-- the definition of the procedure.
-- ================================================
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
CREATE PROCEDURE <Procedure_Name, sysname, ProcedureName> 
 -- Add the parameters for the stored procedure here
 <@Param1, sysname, @p1> <Datatype_For_Param1, , int> = <Default_Value_For_Param1, , 0>, 
 <@Param2, sysname, @p2> <Datatype_For_Param2, , int> = <Default_Value_For_Param2, , 0>
AS
BEGIN
 -- SET NOCOUNT ON added to prevent extra result sets from
 -- interfering with SELECT statements.
 SET NOCOUNT ON;

    -- Insert statements for procedure here
 SELECT <@Param1, sysname, @p1>, <@Param2, sysname, @p2>
END
GO

3. 修改默认内容里面的相关参数如下：

-- ================================================
-- Template generated from Template Explorer using:
-- Create Procedure (New Menu).SQL
--
-- Use the Specify Values for Template Parameters 
-- command (Ctrl-Shift-M) to fill in the parameter 
-- values below.
--
-- This block of comments will not be included in
-- the definition of the procedure.
-- ================================================
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[pr_Slowsqllog_Exp] 
AS
BEGIN
SELECT
(total_elapsed_time  execution_count)/1000 N'平均时间ms'
,total_elapsed_time/1000 N'总花费时间ms'
,total_worker_time/1000 N'所用的CPU总时间ms'
,total_physical_reads N'物理读取总次数'
,total_logical_reads/execution_count N'每次逻辑读次数'
,total_logical_reads N'逻辑读取总次数'
,total_logical_writes N'逻辑写入总次数'
,execution_count N'执行次数'
,SUBSTRING(st.text, (qs.statement_start_offset/2) + 1,
((CASE statement_end_offset
WHEN -1 THEN DATALENGTH(st.text)
ELSE qs.statement_end_offset END
- qs.statement_start_offset)/2) + 1) N'执行语句'
,creation_time N'语句编译时间'
,last_execution_time N'上次执行时间'
FROM
sys.dm_exec_query_stats AS qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
WHERE
SUBSTRING(st.text, (qs.statement_start_offset/2) + 1,
((CASE statement_end_offset
WHEN -1 THEN DATALENGTH(st.text)
ELSE qs.statement_end_offset END
- qs.statement_start_offset)/2) + 1) not like '%fetch%'
AND (total_elapsed_time  execution_count)/1000 > 100
ORDER BY
total_elapsed_time  execution_count DESC;
END
GO

4. 点击执行创建存储过程：

设置定时任务自动执行导出

1. 启动SQLserver代理

【若已启动跳过此步】

启动方式：

计算机右键–管理–服务和应用程序–服务，搜索sql server 代理–右键启动。

2. 新建作业

在SQL Server Management Studio中，SQL Server代理-作业-新建作业。

2.1 常规】为作业定义名称

2.2【步骤】新建

2.2.1 常规设置

为步骤命名;建议数据库+作业名称；
类型选择Transact-SQL 脚本（T-SQL）；
选择要连接的数据库；
填写要执行的命令 exec 存储过程名称,例如：exec pr_Slowsqllog_Exp。

2.2.2 高级设置

提前创建慢日志存放文件夹；
选择已经创建的文件夹；

自定义文件名，后缀为txt格式；

点确定。

2.3【计划】新增计划

为作业计划命名;建议数据库+作业名称；
执行：每天；
执行一次，时间设置；
点确定。

编辑bat脚本send_slow.bat

注意：

DATADIR：为新建罪业中新建的日志文件路径；
SLOWLOG：日志名称文件新建作业生成的日志名称。

send_slow.bat
set DATADIR=D:\slowsqllog
set SLOWLOG=crm_mscrm_slowsqlog.txt
set MAIL_SER=mail.xxx.com
set MAIL_FM=was@xxx.com
set MAIL_LIST=aaa@xxx.com
set MAIL_CC1=bbb@xxx.com
set MAIL_CC2=ccc@xxx.com
set MAIL_SUB=slowlog
set MAIL_BODY=This is a SQL server slowsqllog.

sendEmail -s %MAIL_SER% -f %MAIL_FM% -t %MAIL_LIST% -cc %MAIL_CC1% -cc %MAIL_CC2% -u %MAIL_SUB% -m %MAIL_BODY% -xu  was -xp 654321  -a  %DATADIR%\%SLOWLOG%

windows设置定时计划

注意：定时计划发送邮件的时间一定要比sql server 数据库作业的时间晚，建议晚半个小时。

本文作者：李柯林（上海新炬王翦团队）

本文来源：“IT那活儿”公众号

存储过程 sqlserver数据库

文章转载自IT那活儿，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。