Sqlserver使用游标循环查询所有sqlserver error日志带有Exception的报错

最新推荐文章于 2023-06-13 09:01:59 发布

lusklusklusk

最新推荐文章于 2023-06-13 09:01:59 发布

阅读量586

点赞数

分类专栏： sqlserver 文章标签： sqlserver 数据库 database

本文链接：https://blog.csdn.net/lusklusklusk/article/details/122350042

版权

sqlserver 专栏收录该内容

65 篇文章 1 订阅

订阅专栏

CREATE PROCEDURE [server].[usp_CheckSQLFatalExceptions]
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @TSQL NVARCHAR(MAX);

    DECLARE @ServerLog TABLE
    (
        LogDate DATETIME,
        ProcessInfo NVARCHAR(64),
        MessageText NVARCHAR(2000)
    );

    DECLARE @DumpLog TABLE
    (
        ServerName sysname,
        LogDate DATETIME,
        ProcessInfo NVARCHAR(64),
        MessageText NVARCHAR(2000)
    );

    DECLARE RunPerServer CURSOR FOR
    SELECT L.ServerID,
           L.LogicalName
    FROM [server].List L
        INNER JOIN [server].Environments E
            ON E.EnvironmentID = L.EnvironmentID
               AND E.Environment = 'Production'
        INNER JOIN [server].[Types] T
            ON T.TypeID = L.TypeID
               AND T.[Type] = 'MSSQL'
    WHERE L.Monitor = 1 and LogicalName not in ('DBPROD104\FS2','FILESHAREDBALIAS');

    DECLARE @ServerID INT,
            @LogicalName sysname;

    DECLARE @LogCheckStart DATETIME = DATEADD(MINUTE, -15, GETDATE()),
            @LogCheckEnd DATETIME = GETDATE();

    OPEN RunPerServer;
    FETCH NEXT FROM RunPerServer
    INTO @ServerID,
         @LogicalName;

    WHILE @@FETCH_STATUS = 0
    BEGIN
        SET @TSQL
            = N'EXECUTE ' + QUOTENAME(@LogicalName)
              + N'.master.sys.xp_readerrorlog 0, 1, N''EXCEPTION'', NULL, @LogCheckStart, @LogCheckEnd, ''ASC'';';

		BEGIN TRY
			INSERT INTO @ServerLog
			EXECUTE sp_executesql @TSQL,
								  N'@LogCheckStart DATETIME, @LogCheckEnd DATETIME',
								  @LogCheckStart = @LogCheckStart,
								  @LogCheckEnd = @LogCheckEnd;
		END TRY
		BEGIN CATCH
			PRINT 'COULD NOT QUERY ' + @LogicalName + N': ' + ERROR_MESSAGE();
		END CATCH

        INSERT INTO @DumpLog
        (
            ServerName,
            LogDate,
            ProcessInfo,
            MessageText
        )
        SELECT @LogicalName,
               LogDate,
               ProcessInfo,
               MessageText
        FROM @ServerLog;

        DELETE FROM @ServerLog;

        FETCH NEXT FROM RunPerServer
        INTO @ServerID,
             @LogicalName;
    END;

    CLOSE RunPerServer;
    DEALLOCATE RunPerServer;

    IF NOT EXISTS (SELECT TOP 1 1 FROM @DumpLog)
        RETURN;

    DECLARE @Body VARCHAR(MAX)
        = '<html>
<body style="font-family: Calibri">
<table style="font-family: Calibri; border-collapse: collapse">
	<thead style="font-size: 14px; color:#FFF">
		<tr>
			<th style="background:#c0392b; border:1px solid #bdc3c7; padding:6px 10px">Server</th>
			<th style="background:#c0392b; border:1px solid #bdc3c7; padding:6px 10px">Log Date</th>
			<th style="background:#c0392b; border:1px solid #bdc3c7; padding:6px 10px">Process Info</th>
			<th style="background:#c0392b; border:1px solid #bdc3c7; padding:6px 10px">Messsage</th>
		</tr>
	</thead>
	<tbody style="font-size:12px; color:#333">
'   ;

    SELECT @Body += '		<tr>
			<td style="border:1px solid #bdc3c7; padding:6px 10px">' + ServerName
                    + '</td>
			<td style="border:1px solid #bdc3c7; padding:6px 10px">' + CONVERT(NVARCHAR(32), LogDate)
                    + '</td>
			<td style="border:1px solid #bdc3c7; padding:6px 10px">' + ProcessInfo
                    + '</td>
			<td style="border:1px solid #bdc3c7; padding:6px 10px">' + MessageText + '</td>
		</tr>
'
    FROM @DumpLog
    ORDER BY ServerName ASC,
             LogDate DESC;

    SET @Body += '	</tbody>
</table>
</body>
</html>';

    EXECUTE msdb.dbo.sp_send_dbmail @subject = N'SQL Fatal Exception Alert',
                                    @recipients = N'dba@will.com;Operations@will.com',
                                    @body = @Body,
                                    @body_format = N'HTML',
                                    @importance = N'HIGH';

END;

lusklusklusk

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
Sqlserver使用游标循环查询所有sqlserver error日志带有Exception的报错

CREATE PROCEDURE [server].[usp_CheckSQLFatalExceptions]ASBEGINSET NOCOUNT ON;DECLARE @TSQL NVARCHAR(MAX);DECLARE @ServerLog TABLE( LogDate DATETIME, ProcessInfo NVARCHAR(64), MessageText NVARCHAR(2000));DECLARE @DumpLog TABLE( Se
复制链接

扫一扫

专栏目录