提取 SQL Server 的日志信息并发送邮件(SQL Server: Controlling SQL Server Log Information)

By | 2018年7月11日

For DBAs, SQL Server Log is the main source to troubleshoot problems related to SQL Server. It contains user-defined events and certain system events.  By default 6 files are created for an instance and are recycled once sql server is restarted or you can force for new sql server log file with following simple statement.

EXEC sp_cycle_errorlog

Number of SQL Server Log files can be increased up to 99, while minimum value is 6.

USE [master]
GO
EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE',N'Software\Microsoft\MSSQLServer\MSSQLServer', N'NumErrorLogs', REG_DWORD, 99
Or you can change these values through graphical interface, move your mouse pointer to SQL Server Log and right click to choose “Configure” option.
Not all the messages or errors are critical that should be followed, and somehow, quantity of such messages is quite large in a normal SQL Server Log file and finding messages and errors of critical nature are sometime a big problem itself. 
It can be managed by extracting necessary errors and messages and saving in a separate user defined table and later on it could be queried, or most DBAs like to send these messages and errors through HTML mail (This is a more appropriate way, as you need not to visit your SQL Server instance and necessary information can be found in your inbox).

Following is the script which can be used to extract necessary information from SQL Server Log and send through HTML mail.
-- Table variable to hold intermediate data
DECLARE @ReportSQLErrorLogs TABLE
    (
      [log_date] [datetime] NULL,
      [processinfo] [varchar](255) NULL,
      [processtext] [text] NULL
    )

DECLARE @NumErrorLogs INT,
    @CurrentLogNum INT

SET @CurrentLogNum = 0

-- Get total number of log files from registry
EXEC master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE',
    N'Software\Microsoft\MSSQLServer\MSSQLServer', N'NumErrorLogs',
    @NumErrorLogs OUTPUT

SELECT @NumErrorLogs = ISNULL(@NumErrorLogs, 6)
 -- NULL in registry entry for Error Log files mean default of 6 value

WHILE @CurrentLogNum < @NumErrorLogs
    BEGIN
        INSERT INTO @ReportSQLErrorLogs
                EXEC master..xp_readerrorlog @CurrentLogNum
        PRINT @CurrentLogNum
        SELECT @CurrentLogNum = @CurrentLogNum + 1
    END
 

DECLARE @Body VARCHAR(MAX),
    @TableHead VARCHAR(1000),
    @TableTail VARCHAR(1000)


SET @TableTail = '</table></body></html>' ;

SET @TableHead = '<html><head>' + '<style>'
    + 'td {border: solid black 1px;padding-left:5px;padding-right:5px;padding-top:1px;padding-bottom:1px;font-size:11pt;} '
    + '</style>' + '</head>'
    + '<body><table cellpadding=0 cellspacing=0 border=0>'
    + '<tr><td align=center bgcolor=#E6E6FA><b>ROW ID</b></td>'
    + '<td align=center bgcolor=#E6E6FA><b>Log Date</b></td>'
    + '<td align=center bgcolor=#E6E6FA><b>Process Info</b></td>'
    + '<td align=center bgcolor=#E6E6FA><b>Process Text</b></td></tr>' ;
---Create HTML mail Body
SELECT  @Body = ( SELECT    td = row_number() OVER ( ORDER BY I.row_id ),
                            td = I.log_date,
                            '',
                            td = ISNULL(I.processinfo, ''),
                            '',
                            td = ISNULL(I.processtext, ''),
                            ''
                  FROM      #ReportSQLErrorLogs I ( NOLOCK )
                  --- Filter only necessary information
                  WHERE     I.processtext NOT LIKE '%error log%'
                            AND I.processtext NOT LIKE '%Database backed up%'
                            AND I.processtext NOT LIKE '%Logging SQL Server messages in file %'
                            AND I.processtext NOT LIKE '%Authentication mode%'
                            AND I.processtext NOT LIKE '%System Manufacturer%'
                            AND I.processtext NOT LIKE '%All rights reserved.%'
                            AND I.processtext NOT LIKE '%(c) 2005 Microsoft Corporation.%'
                            AND I.processtext NOT LIKE '%Microsoft SQL Server 2008 (SP1)%'
                            AND I.processtext NOT LIKE '%SQL Trace ID%'
                            AND I.processtext NOT LIKE '%full-text catalog%'
                            AND I.processtext NOT LIKE '%Server process ID is%'
                            AND I.processtext NOT LIKE '%starting up database%'
                            AND I.processtext NOT LIKE '%found 0 errors%'
							-- To extract information for last 24 hours
							AND DATEDIFF(HH,I.log_date,GETDATE()) <=24
                FOR
                  XML RAW('tr'),
                      ELEMENTS
                )

-- Replace the entity codes and row numbers
SET @Body = REPLACE(@Body, '_x0020_', SPACE(1))
SET @Body = REPLACE(@Body, '_x003D_', '=')
SET @Body = REPLACE(@Body, '<tr><TRRow>1</TRRow>', '<tr bgcolor=#C6CFFF>')
SET @Body = REPLACE(@Body, '<TRRow>0</TRRow>', '')

SELECT  @Body = @TableHead + @Body + @TableTail

--- Send HTML mail
EXEC msdb.dbo.sp_send_dbmail 
@recipients = 'aasim.rokhri@gmail.com', -- Mention email addresses separated by semicolon
    @subject = 'SQL SERVER LOGS REPORT', 
    @profile_name = 'DBA', -- Change profile name according to your own
    @body = @Body, 
    @body_format = 'HTML' ; --Mail format should be HTML

Mail in you inbox would look like following.

对一个 DBA 来说,SQL Server 的日志是用来诊断问题的主要资源。它包含了用户自定义事件和一些系统事件。默认每个实例创建 6 个文件用于循环记录日志。一旦服务器重启就会重新循环使用这些日志文件,也可以通过下面命令来重新循环使用日志:

EXEC sp_cycle_errorlog

SQL Server 的日志文件可多达 99 个,最少是 6 个,可通过下面命令来增加日志文件:

USE [master]
GO
EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE',N'Software\Microsoft\MSSQLServer\MSSQLServer', N'NumErrorLogs', REG_DWORD, 99

当然你也可以通过图形界面来修改这个数值,只需右键 SQL Server 日志然后选择配置项即可。

并不是日志的每一条信息都是关键的,要从中查找有价值的信息是个很大的问题。

可将一些特定的信息抽取出来然后保存到一个独立的用户自定义表中,以便日后查询。也有很多 DBA 喜欢将一些事件和错误信息通过 HTML 邮件发送到指定邮箱,这样你就可以不打开 SQL Server 就可以得知问题发生的详情。

下面的脚本可以帮你从 SQL Server 日志中出去必要信息然后使用邮件发送到指定邮箱:

-- Table variable to hold intermediate data
DECLARE @ReportSQLErrorLogs TABLE
    (
      [log_date] [datetime] NULL,
      [processinfo] [varchar](255) NULL,
      [processtext] [text] NULL
    )

DECLARE @NumErrorLogs INT,
    @CurrentLogNum INT

SET @CurrentLogNum = 0

-- Get total number of log files from registry
EXEC master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE',
    N'Software\Microsoft\MSSQLServer\MSSQLServer', N'NumErrorLogs',
    @NumErrorLogs OUTPUT

SELECT @NumErrorLogs = ISNULL(@NumErrorLogs, 6)
 -- NULL in registry entry for Error Log files mean default of 6 value

WHILE @CurrentLogNum < @NumErrorLogs
    BEGIN
        INSERT INTO @ReportSQLErrorLogs
                EXEC master..xp_readerrorlog @CurrentLogNum
        PRINT @CurrentLogNum
        SELECT @CurrentLogNum = @CurrentLogNum + 1
    END
 

DECLARE @Body  VARCHAR(MAX),
    @TableHead VARCHAR(1000),
    @TableTail VARCHAR(1000)


SET @TableTail = '</table></body></html>' ;

SET @TableHead = '<html><head>' + '<style>'
    + 'td {border: solid black 1px;padding-left:5px;padding-right:5px;padding-top:1px;padding-bottom:1px;font-size:11pt;} '
    + '</style>' + '</head>'
    + '<body><table cellpadding=0 cellspacing=0 border=0>'
    + '<tr><td align=center bgcolor=#E6E6FA><b>ROW ID</b></td>'
    + '<td align=center bgcolor=#E6E6FA><b>Log Date</b></td>'
    + '<td align=center bgcolor=#E6E6FA><b>Process Info</b></td>'
    + '<td align=center bgcolor=#E6E6FA><b>Process Text</b></td></tr>' ;
---Create HTML mail Body
SELECT  @Body  = ( SELECT    td = row_number() OVER ( ORDER BY I.row_id ),
                            td = I.log_date,
                            '',
                            td = ISNULL(I.processinfo, ''),
                            '',
                            td = ISNULL(I.processtext, ''),
                            ''
                  FROM      #ReportSQLErrorLogs I ( NOLOCK )
                  --- Filter only necessary information
                  WHERE     I.processtext NOT LIKE '%error log%'
                            AND I.processtext NOT LIKE '%Database backed up%'
                            AND I.processtext NOT LIKE '%Logging SQL Server messages in file %'
                            AND I.processtext NOT LIKE '%Authentication mode%'
                            AND I.processtext NOT LIKE '%System Manufacturer%'
                            AND I.processtext NOT LIKE '%All rights reserved.%'
                            AND I.processtext NOT LIKE '%(c) 2005 Microsoft Corporation.%'
                            AND I.processtext NOT LIKE '%Microsoft SQL Server 2008 (SP1)%'
                            AND I.processtext NOT LIKE '%SQL Trace ID%'
                            AND I.processtext NOT LIKE '%full-text catalog%'
                            AND I.processtext NOT LIKE '%Server process ID is%'
                            AND I.processtext NOT LIKE '%starting up database%'
                            AND I.processtext NOT LIKE '%found 0 errors%'
							-- To extract information for last 24 hours
							AND DATEDIFF(HH,I.log_date,GETDATE()) <=24
                FOR
                  XML RAW('tr'),
                      ELEMENTS
                )

-- Replace the entity codes and row numbers
SET @Body  = REPLACE(@Body , '_x0020_', SPACE(1))
SET @Body  = REPLACE(@Body , '_x003D_', '=')
SET @Body  = REPLACE(@Body , '<tr><TRRow>1</TRRow>', '<tr bgcolor=#C6CFFF>')
SET @Body  = REPLACE(@Body , '<TRRow>0</TRRow>', '')

SELECT  @Body  = @TableHead + @Body  + @TableTail

--- Send HTML mail
EXEC msdb.dbo.sp_send_dbmail 
@recipients = 'aasim.rokhri@gmail.com', -- Mention email addresses separated by semicolon
    @subject = 'SQL SERVER LOGS REPORT', 
    @profile_name = 'DBA', -- Change profile name according to your own
    @body  = @Body , 
    @body_format = 'HTML' ; --Mail format should be HTML

然后就是检查你的收件箱即可。


via:oschina

发表评论

电子邮件地址不会被公开。 必填项已用*标注