Suppress messages in SQL log

Previously we had looked at how to configure alerts to log the messages that we are interested in picking up. In this previous post I very briefly showed how to add logging for messages that were not normally logged such as a permission denied.

SELECT * FROM sys.messages WHERE [message_id] = 229
The %ls permission was denied on the object '%.*ls', database '%.*ls', schema '%.*ls'.

sp_altermessage (Transact-SQL)
http://msdn.microsoft.com/en-us/library/ms175094.aspx

EXEC msdb.dbo.sp_altermessage 229,'WITH_LOG','true';

But just as there are extra messages you want to see there are also those that perhaps you do not. You can see what is, and is not logged by viewing sys.messages.

The example below will suppress the message.

EXEC msdb.dbo.sp_altermessage 229,'WITH_LOG','false';
GO
SELECT * FROM sys.messages WHERE [message_id] = 229;

Previously the example above suppressed message id 3197 and 3198 which are generated by Data Protection Manager (DPM). However it would appear that these can not be suppressed. The most likely reason being that the messages when generated are called WITH_LOG meaning that they always log regardless.

Finally for those who want to suppress successful backup messages you can use trace flag 3226.

Trace Flags (Transact-SQL)
http://technet.microsoft.com/en-us/library/ms188396.aspx

By default, every successful backup operation adds an entry in the SQL Server error log and in the system event log. If you create very frequent log backups, these success messages accumulate quickly, resulting in huge error logs in which finding other messages is problematic.

With this trace flag, you can suppress these log entries. This is useful if you are running frequent log backups and if none of your scripts depend on those entries.

Leave a Reply