SQL Server: Controlling 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 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.

Mail in you inbox would look like following.