Log Errors in SQL Server(Management\SQL Server Log)
In SQL Server if you need to log all errors ( Or any given errors ) then there is no easy way to do it using graphical interface. Following SQL script will help you to log all errors in SQL Server log file. This script is MSSQL configuration script installation level. After execute this it will configure all errors log to MSSQL log files. Note: This is not recommended to execute in Production server or very busy low performance test db servers.
(B’cos after install this script it will log all errors, it cost extra CPU and disk space)USE [master]
GO
EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'NumErrorLogs', REG_DWORD, 10
GO
--EXEC sp_altermessage 8134, 'WITH_LOG', 'true';
GO
DECLARE @message_id nvarchar(20);
DECLARE SYS_messages CURSOR FOR
SELECT CAST(message_id AS nvarchar(20)) FROM sys.messages WHERE language_id=1033 ORDER BY message_id;
OPEN SYS_messages;
FETCH NEXT FROM SYS_messages INTO @message_id;
WHILE @@FETCH_STATUS = 0 BEGIN
PRINT @message_id
EXEC sp_altermessage @message_id, 'WITH_LOG', 'true';
FETCH NEXT FROM SYS_messages INTO @message_id;
END
CLOSE SYS_messages;
DEALLOCATE SYS_messages;
GO
