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.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| -- 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 |
No comments:
Post a Comment
All suggestions are welcome