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)
        = '

<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>

';

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;

Sqlserver使用遊標迴圈查詢所有sqlserver error日誌帶有Exception的報錯

相關文章