检测锁状态并同步azure云分享

最新推荐文章于 2024-08-13 00:03:06 发布

喝茶品人生

最新推荐文章于 2024-08-13 00:03:06 发布

阅读量167

点赞数 3

文章标签： azure 数据库服务器

本文链接：https://blog.csdn.net/qq_35394977/article/details/130103371

版权

该脚本用于检查大众云服务器上的数据库锁运行状态，并将结果同步发送到阿里云邮箱。当存在锁定资源时，脚本会生成详细的锁信息报告，包括数据库名称、资源类型、请求模式等，并通过sp_send_dbmail存储过程发送邮件。若无锁定资源，脚本则打印相应消息。确保在阿里云环境中配置正确的邮件配置。

摘要由CSDN通过智能技术生成

Hi，这里给大家分享今日完成的脚本案例，背景是大众云服务器检测数据库锁运行状态并同步至阿里云邮箱上。

USE master;
GO

DECLARE @lock_info TABLE
(
    database_name NVARCHAR(128) NOT NULL,
    resource_type NVARCHAR(60) NOT NULL,
    resource_description NVARCHAR(256) NOT NULL,
    request_mode_type NVARCHAR(60) NOT NULL,
    request_status_type NVARCHAR(60) NOT NULL,
    request_session_id INT NOT NULL,
    blocking_session_id INT NOT NULL,
    wait_time_ms INT NOT NULL,
    wait_type NVARCHAR(60) NULL,
    wait_resource NVARCHAR(256) NULL
);

INSERT INTO @lock_info
(
    database_name, resource_type, resource_description, request_mode_type, request_status_type,
    request_session_id, blocking_session_id, wait_time_ms, wait_type, wait_resource
)
SELECT
    DB_NAME(l.resource_database_id) AS database_name,
    l.resource_type,
    l.resource_description,
    r.request_mode AS request_mode_type,
    r.request_status AS request_status_type,
    r.session_id AS request_session_id,
    r.blocking_session_id,
    r.wait_time AS wait_time_ms,
    r.wait_type,
    r.wait_resource
FROM sys.dm_tran_locks AS l
INNER JOIN sys.dm_exec_requests AS r ON l.request_session_id = r.session_id
WHERE l.resource_type <> 'DATABASE';

IF EXISTS (SELECT * FROM @lock_info)
BEGIN
    DECLARE @msg_body NVARCHAR(MAX);
    SET @msg_body = '以下是当前数据库锁状态信息：' + CHAR(13) + CHAR(10) + CHAR(13) + CHAR(10);
    SET @msg_body = @msg_body + '数据库名称 | 资源类型 | 资源描述 | 请求模式 | 请求状态 | 请求会话ID | 阻塞会话ID | 等待时间（毫秒） | 等待类型 | 等待资源' + CHAR(13) + CHAR(10);
    SET @msg_body = @msg_body + '----------------------------------------------------------------------------------------------------------------------------------------' + CHAR(13) + CHAR(10);

    DECLARE @db_name NVARCHAR(128);
    DECLARE @resource_type NVARCHAR(60);
    DECLARE @resource_description NVARCHAR(256);
    DECLARE @request_mode_type NVARCHAR(60);
    DECLARE @request_status_type NVARCHAR(60);
    DECLARE @request_session_id INT;
    DECLARE @blocking_session_id INT;
    DECLARE @wait_time_ms INT;
    DECLARE @wait_type NVARCHAR(60);
    DECLARE @wait_resource NVARCHAR(256);

    DECLARE lock_cursor CURSOR FOR
        SELECT
            database_name, resource_type, resource_description, request_mode_type, request_status_type,
            request_session_id, blocking_session_id, wait_time_ms, wait_type, wait_resource
        FROM @lock_info;
    OPEN lock_cursor;
    FETCH NEXT FROM lock_cursor INTO
        @db_name, @resource_type, @resource_description, @request_mode_type, @request_status_type,
        @request_session_id, @blocking_session_id, @wait_time_ms, @wait_type, @wait_resource;
    WHILE @@FETCH_STATUS = 0
    BEGIN
        SET @msg_body = @msg_body +
            CAST(@db_name AS NVARCHAR(128)) + ' | ' +
            CAST(@resource_type AS NVARCHAR(60)) + ' | ' +
            CAST(@resource_description AS NVARCHAR(256)) + ' | ' +
            CAST(@request_mode_type AS NVARCHAR(60)) + ' | ' +
            CAST(@request_status_type AS NVARCHAR(60)) + ' | ' +
            CAST(@request_session_id AS NVARCHAR(20)) + ' | ' +
            CAST(@blocking_session_id AS NVARCHAR(20)) + ' | ' +
            CAST(@wait_time_ms AS NVARCHAR(20)) + ' | ' +
            CAST(@wait_type AS NVARCHAR(60)) + ' | ' +
            CAST(@wait_resource AS NVARCHAR(256)) + CHAR(13) + CHAR(10);
        FETCH NEXT FROM lock_cursor INTO
            @db_name, @resource_type, @resource_description, @request_mode_type, @request_status_type,
            @request_session_id, @blocking_session_id, @wait_time_ms, @wait_type, @wait_resource;
    END
    CLOSE lock_cursor;
    DEALLOCATE lock_cursor;

    EXEC msdb.dbo.sp_send_dbmail
        @profile_name = '<YOUR_EMAIL_PROFILE>',
        @recipients = '<YOUR_EMAIL_ADDRESS>',
        @subject = '数据库锁状态监测报告',
        @body = @msg_body;
END
ELSE
BEGIN
    PRINT '当前没有锁定的资源。';
END

提示：阿里云部署需要正确填写profile的名称和接收人地址，dns配置要同步好，如果对上述代码有疑问可以提出，已完成发布测试。