SQL Server - 使用聚集索引时如何存储数据页

Question

Michael Cherevko

Asked: 2016-05-22 21:51:10 +0800 CST2016-05-22 21:51:10 +0800 CST 2016-05-22 21:51:10 +0800 CST

捕获两条错误消息/扔到表中

772

我的目标是将备份作业中的错误捕获到监控表中。
问题是，有些情况下备份语句返回多个错误，因此 ERROR_MESSAGE() 是不够的：

消息 3201，级别 16，状态 1，第 1 行无法打开备份设备“...”操作系统错误 3（系统找不到指定的路径。）。

消息 3013，级别 16，状态 1，第 1 行备份数据库异常终止。

我可以使用 throw 来捕获这两条消息，但是有没有一种简单的方法可以将 throw 输出插入到表中或以另一种方式克服这个问题？

3 个回答

Voted

Dave Mason · Answer 1 · 2016-09-16T06:23:44+08:00

您可以使用扩展事件来增强错误处理，以克服您遇到的 TRY...CATCH 缺点。设计模式涉及以下步骤：

创建并启动扩展事件会话：它将捕获sqlserver.error_reported主要由 SPID 过滤的事件。
TRY在块中执行语句。
在块内（或之后）CATCH，读取 XEvents 会话数据。
使用可用数据酌情响应错误。
停止并删除 XEvents 会话。

这是一个可以从 SSMS 以单批次手动运行的示例。只需确保将脚本中所有出现的“2016”替换为您的 SPID。

CREATE EVENT SESSION [Error Handling Session(2016)]
ON SERVER 
ADD EVENT sqlserver.error_reported
(
    ACTION(
        sqlserver.session_id,
        sqlserver.sql_text
    )
    WHERE [package0].[not_equal_unicode_string]([message],N'''''') 
    AND [severity]>(10) 
    AND [sqlserver].[session_id]=(2016)
) 
ADD TARGET package0.ring_buffer
WITH (
    --ALLOW_SINGLE_EVENT_LOSS
    --NO_EVENT_LOSS
    EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,

    MAX_MEMORY=4096 KB,
    MAX_DISPATCH_LATENCY=1 SECONDS,
    MAX_EVENT_SIZE=0 KB,
    MEMORY_PARTITION_MODE=NONE,
    TRACK_CAUSALITY=ON,
    STARTUP_STATE=OFF
);

ALTER EVENT SESSION [Error Handling Session(2016)] ON SERVER STATE=START;

BEGIN TRY
    BACKUP DATABASE master
    TO DISK = 'master.diff.bak'
    WITH DIFFERENTIAL;
END TRY
BEGIN CATCH
    DECLARE @XEData XML

    SELECT @XEData = CAST(xet.target_data AS XML)
    FROM sys.dm_xe_session_targets AS xet
    JOIN sys.dm_xe_sessions AS xe
        ON (xe.address = xet.event_session_address)
    WHERE xe.name = 'Error Handling Session(2016)';

    /*
    Check value of "totalEventsProcessed" to ensure events have been 
    dispatched to event session target (ring_buffer).
    If no events have been processed, delay for a period of MAX_DISPATCH_LATENCY +1 (in seconds).
    */
    IF @XEData.value('(/RingBufferTarget/@totalEventsProcessed)[1]', 'INT') = 0
    BEGIN
        WAITFOR DELAY '00:00:02';

        SELECT @XEData = CAST(xet.target_data AS XML)
        FROM sys.dm_xe_session_targets AS xet
        JOIN sys.dm_xe_sessions AS xe
            ON (xe.address = xet.event_session_address)
        WHERE xe.name = 'Error Handling Session(2016)';
    END

    --Comment/uncomment as desired to show the formatted XML session data.
    SELECT @XEData;

    --Shred the XML. Do whatever you want with this data: 
    -- log it to a table, create and send an email alert, etc.
    SELECT 
        x.c.value(N'(@name)[1]', N'NVARCHAR(MAX)') AS EventName,
        x.c.value(N'(@timestamp)[1]', N'datetime') AS EventTime,
        x.c.value(N'(data[@name="error_number"]/value)[1]', N'NVARCHAR(MAX)') AS ErrorNumber,
        x.c.value(N'(data[@name="severity"]/value)[1]', N'NVARCHAR(MAX)') AS Severity,
        x.c.value(N'(data[@name="state"]/value)[1]', N'NVARCHAR(MAX)') AS [State],
        x.c.value(N'(data[@name="user_defined"]/value)[1]', N'NVARCHAR(MAX)') AS UserDefined,
        x.c.value(N'(data[@name="category"]/text)[1]', N'NVARCHAR(MAX)') AS Category,
        x.c.value(N'(data[@name="destination"]/text)[1]', N'NVARCHAR(MAX)') AS Destination,
        x.c.value(N'(data[@name="is_intercepted"]/value)[1]', N'NVARCHAR(MAX)') AS IsIntercepted,
        x.c.value(N'(data[@name="message"]/value)[1]', N'NVARCHAR(MAX)') AS [Message],
        x.c.value(N'(action[@name="sql_text"]/value)[1]', N'NVARCHAR(MAX)') AS SqlText,
        x.c.value(N'(action[@name="session_id"]/value)[1]', N'NVARCHAR(MAX)') AS SessionId
    FROM @XEData.nodes('//RingBufferTarget/event') AS x(c)
    --WHERE x.c.value(N'(data[@name="destination"]/text)[1]', N'NVARCHAR(MAX)') = 'USER'

END CATCH

ALTER EVENT SESSION [Error Handling Session(2016)] ON SERVER STATE=STOP;
DROP EVENT SESSION [Error Handling Session(2016)] ON SERVER;
GO

运行脚本后，您的 SSMS 输出应类似于以下内容：

那里有很多活动部件。但你也许可以让它为你工作。我写了一些关于 TRY...CATCH 的相关博客文章，可能会有所帮助：

TRY...CATCH 未兑现的承诺

使用扩展事件增强 T-SQL 错误处理

第 2 部分：使用扩展事件增强 T-SQL 错误处理

IT Thug Ninja · Answer 2 · 2016-05-22T22:40:27+08:00

问题是，有些情况下备份语句返回多个错误，因此 ERROR_MESSAGE() 是不够的。

在用于使用备份设备备份数据库的 SQL 代理作业中，无论失败还是成功，只要让每个步骤都进入下一步。但是，在最后一步中包含以下逻辑，并告诉它在失败时报告失败并在成功时报告成功。

这将通过电子邮件将来自该作业的 msdb 表中的失败详细信息通过电子邮件发送给您，正如您在 SQL 代理作业历史记录区域中看到的当前日期记录一样。您也可以在收到电子邮件通知时手动检查 SQL 代理历史记录，但这非常适合我设置它的需要。

我相信您也可以使用该逻辑并进行一些更改以进一步满足您的需求。在我的情况下，备份设备的每个备份作业都是它自己的单独步骤，无论结果如何，每个步骤都进入下一步，最后一步正如我上面指出的那样，足以进行故障监控。

如果我不在办公室或通过 VPN 连接，通过电子邮件发送的 HTML 表格数据将让我知道这是否需要注意等等检查 Outlook Web 邮件。

WAITFOR DELAY '00:00:10'  ----ten second pause to ensure everything needed is written to table
SELECT  step_name, message
FROM    msdb.dbo.sysjobhistory
WHERE   instance_id > COALESCE((SELECT MAX(instance_id) FROM msdb.dbo.sysjobhistory
                                WHERE job_id = $(ESCAPE_SQUOTE(JOBID)) AND step_id = 0), 0)
        AND job_id = $(ESCAPE_SQUOTE(JOBID))
        AND run_status <> 1 -- success

IF      @@ROWCOUNT <> 0
BEGIN
        RAISERROR('*** SQL Agent Job Prior Step Failure Occurred ***', 16, 1)

DECLARE @job_name NVARCHAR(256) = (SELECT name FROM msdb.dbo.sysjobs WHERE job_id = $(ESCAPE_SQUOTE(JOBID)))
DECLARE @email_profile NVARCHAR(256) = 'SQLInstance Name Alerts'
DECLARE @emailrecipients NVARCHAR(500) = '[email protected]'
DECLARE @subject NVARCHAR(MAX) = 'SQL Server Agent Job Failure Report: ' + @@SERVERNAME
DECLARE @msgbodynontable NVARCHAR(MAX) = 'SQL Server Agent Job Failure Report For: "' + @job_name + '"'

--Dump report data to a temp table to be put into XML formatted HTML table to email out
SELECT sjh.[server]
    ,sj.NAME
    ,sjh.step_id
    ,sjh.[message]
    ,sjh.run_date
    ,sjh.run_time
INTO #TempJobFailRpt
FROM msdb..sysjobhistory sjh
INNER JOIN msdb..sysjobs sj ON (sj.job_id = sjh.job_id)
WHERE run_date = convert(INT, convert(VARCHAR(8), getdate(), 112))
    AND run_status != 4 -- Do not show status of 4 meaning in progress steps
    AND run_status != 1 -- Do not show status of 1 meaning success
    AND NAME = @job_name
ORDER BY run_date

IF EXISTS (
        SELECT *
        FROM #TempJobFailRpt
        )
BEGIN

-----Build report to HTML formatted email using FOR XML PATH
DECLARE @tableHTML NVARCHAR(MAX) = '
<html>
<body>
    <H1>' + @msgbodynontable + '</H1>
        <table border="1" style=
        "background-color: #C0C0C0; border-collapse: collapse">
        <caption style="font-weight: bold">
                ****** Below is the SQL Agent history detail from the SQL Agent job named: ''' + @job_name + ''' . ******
        </caption>

<tr>
    <th style="width:25%; text-decoration: underline">SQL Instance</th>
    <th style="text-decoration: underline">Job Name</th>
    <th style="text-decoration: underline">Step</th>
    <th style="text-decoration: underline">Message Text</th>
    <th style="text-decoration: underline">Job Run Date</th>
    <th style="text-decoration: underline">Job Run Time</th>
</tr>' + CAST((
            SELECT td = [server]
                ,''
                ,td = NAME
                ,''
                ,td = step_id
                ,''
                ,td = [message]
                ,''
                ,td = run_date
                ,''
                ,td = run_time
            FROM #TempJobFailRpt a
            ORDER BY run_date
            FOR XML PATH('tr')
                ,TYPE
                ,ELEMENTS XSINIL -- brings back non isnull NULL column values as empty without making HTML table look odd for empty value otherwise
            ) AS NVARCHAR(MAX)) + '
    </table>
</body>
</html>';

EXEC msdb.dbo.sp_send_dbmail @profile_name = @email_profile
    ,@recipients = @emailrecipients
    ,@subject = @subject
    ,@body = @tableHTML
    ,@body_format = 'HTML'

--Drop Temp table
    DROP TABLE #TempJobFailRpt
END
ELSE
BEGIN
    PRINT '*** No Records Generated ***' 
    DROP TABLE #TempJobFailRpt
END
END

Michael Cherevko · Answer 3 · 2016-09-21T00:37:49+08:00

带有 spid 句柄的 DMason 查询：

DECLARE @Spid varchar(6)
DECLARE @Sql varchar(max)
SELECT @Spid = @@spid
SET @sql = 
'CREATE EVENT SESSION [Error Handling Session(' + @spid + ')]
ON SERVER 
ADD EVENT sqlserver.error_reported
(
    ACTION(
        sqlserver.session_id,
        sqlserver.sql_text
    )
    WHERE [package0].[not_equal_unicode_string]([message],N'''''''''''') 
    AND [severity]>(10) 
    AND [sqlserver].[session_id]=(' + @spid + ')
) 
ADD TARGET package0.ring_buffer
WITH (
    --ALLOW_SINGLE_EVENT_LOSS
    --NO_EVENT_LOSS
    EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,

    MAX_MEMORY=4096 KB,
    MAX_DISPATCH_LATENCY=1 SECONDS,
    MAX_EVENT_SIZE=0 KB,
    MEMORY_PARTITION_MODE=NONE,
    TRACK_CAUSALITY=ON,
    STARTUP_STATE=OFF
);

ALTER EVENT SESSION [Error Handling Session(' + @spid + ')] ON SERVER STATE=START;'
EXEC(@Sql)
BEGIN TRY
    BACKUP DATABASE master
    TO DISK = 'master.diff.bak'
    WITH DIFFERENTIAL;
END TRY
BEGIN CATCH
    DECLARE @XEData XML
    DECLARE @XEName varchar(100) = 'Error Handling Session(' + @spid + ')' 

    SELECT @XEData = CAST(xet.target_data AS XML)
    FROM sys.dm_xe_session_targets AS xet
    JOIN sys.dm_xe_sessions AS xe
        ON (xe.address = xet.event_session_address)
    WHERE xe.name = @XEName;

    /*
    Check value of "totalEventsProcessed" to ensure events have been 
    dispatched to event session target (ring_buffer).
    If no events have been processed, delay for a period of MAX_DISPATCH_LATENCY +1 (in seconds).
    */
    IF @XEData.value('(/RingBufferTarget/@totalEventsProcessed)[1]', 'INT') = 0
    BEGIN
        WAITFOR DELAY '00:00:02';

        SELECT @XEData = CAST(xet.target_data AS XML)
        FROM sys.dm_xe_session_targets AS xet
        JOIN sys.dm_xe_sessions AS xe
            ON (xe.address = xet.event_session_address)
        WHERE xe.name = @XEName;
    END

    --Comment/uncomment as desired to show the formatted XML session data.
    SELECT @XEData;

    --Shred the XML. Do whatever you want with this data: 
    -- log it to a table, create and send an email alert, etc.
    SELECT 
        x.c.value(N'(@name)[1]', N'NVARCHAR(MAX)') AS EventName,
        x.c.value(N'(@timestamp)[1]', N'datetime') AS EventTime,
        x.c.value(N'(data[@name="error_number"]/value)[1]', N'NVARCHAR(MAX)') AS ErrorNumber,
        x.c.value(N'(data[@name="severity"]/value)[1]', N'NVARCHAR(MAX)') AS Severity,
        x.c.value(N'(data[@name="state"]/value)[1]', N'NVARCHAR(MAX)') AS [State],
        x.c.value(N'(data[@name="user_defined"]/value)[1]', N'NVARCHAR(MAX)') AS UserDefined,
        x.c.value(N'(data[@name="category"]/text)[1]', N'NVARCHAR(MAX)') AS Category,
        x.c.value(N'(data[@name="destination"]/text)[1]', N'NVARCHAR(MAX)') AS Destination,
        x.c.value(N'(data[@name="is_intercepted"]/value)[1]', N'NVARCHAR(MAX)') AS IsIntercepted,
        x.c.value(N'(data[@name="message"]/value)[1]', N'NVARCHAR(MAX)') AS [Message],
        x.c.value(N'(action[@name="sql_text"]/value)[1]', N'NVARCHAR(MAX)') AS SqlText,
        x.c.value(N'(action[@name="session_id"]/value)[1]', N'NVARCHAR(MAX)') AS SessionId
    FROM @XEData.nodes('//RingBufferTarget/event') AS x(c)
    --WHERE x.c.value(N'(data[@name="destination"]/text)[1]', N'NVARCHAR(MAX)') = 'USER'

END CATCH
SET @Sql = 
'ALTER EVENT SESSION [Error Handling Session(' + @Spid + ')] ON SERVER STATE=STOP;
DROP EVENT SESSION [Error Handling Session(' + @Spid + ')] ON SERVER;'
EXEC(@sql)
GO

捕获两条错误消息/扔到表中

连接到 PostgreSQL 服务器：致命：主机没有 pg_hba.conf 条目

如何让sqlplus的输出出现在一行中？

选择具有最大日期或最晚日期的日期

如何列出 PostgreSQL 中的所有模式？

列出指定表的所有列

如何在不修改我自己的 tnsnames.ora 的情况下使用 sqlplus 连接到位于另一台主机上的 Oracle 数据库

你如何mysqldump特定的表？

使用 psql 列出数据库权限

如何从 PostgreSQL 中的选择查询中将值插入表中？

如何使用 psql 列出所有数据库和表？

捕获两条错误消息/扔到表中

3 个回答

相关问题