SQL Server: Controlling SQL Server Log Information

aasim abdullah

0.00/5 (No votes)

15 Sep 2012CPOL1 min read

How to control SQL Server Log Information

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 a new SQL Server log file with the following simple statement:

SQL

EXEC sp_cycle_errorlog

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

SQL

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 visit your SQL Server instance and necessary information can be found in your inbox.)

Following is the script which can be used to extract the necessary information from SQL Server Log and send through HTML mail.

SQL

-- 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 your inbox would look like the following:

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)