Generating Error when data size is larger then max text repl size

I recently ran into an issue with where an application tried to do an insert with data over the max text repl size. this time the failed insert was captured by the application log, and I was made aware. I was a little disturbed though to find out the error wasn't captured in the sql server error log, and I don't see it captured any place else.

Any advise on how to capture, and Ideally generate alerts based on the 'Length of LOB data (XXXXXX) to be replicated exceeds configured maximum " would be appreciated.

The SQL Server error log only captures errors having a significant severity (generally 19-25, with exceptions). See the documentation here:

• https://learn.microsoft.com/en-us/sql/relational-databases/errors-events/database-engine-error-severities

Imagine if all divide by zero, ambiguous column, or syntax errors flooded the error log? That log would quickly become an unmanageable mess.

What you can do is use an Alert, which can trigger notifications and other actions whenever an exception with a certain severity (or, in your case, error number) is raised:

• https://learn.microsoft.com/en-us/sql/ssms/agent/alerts

For example:

EXEC sys.sp_add_alert @name = 'repl size error',
  @message_id = 7139, -- I believe this is the right message
  ...

See this doc for sp_add_alert usage:

• https://learn.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-add-alert-transact-sql

StackExchange Database Administrators Q#245549, answer score: 6