I have inherited a new SQL Server database instance.
The SQL Server Agent error Log is full of collation errors - new errors are arising every 10 seconds. But detail of error is minimal - 'cannot resolve collation conflict between' etc...
Now I understand collation conflicts, and given a specific query with a conflict, I would now how to investigate and correct.
How do I identify the query (queries) that are causing the collation conflict?
I have checked SQL Agent Jobs - there are no jobs running often enough to cause the constant errors.
I can use SQL Profiler to highlight errors being posted to the SQL Server log (together with the query), but can't do that for errors posted to SQL Agent Log.
Everywhere on the internet explains how to resolve collation conflicts of a specific query. But I can't even identify what query is 'causing' the collation conflict.
OK, managed to use an Extended Event session to record events for 'Error Reported'.
Found my collation error being reported against the following SQL_text:
which I understand is necessary when you have alerts configured.
I appear to have about 20 or so alerts configured on this instance - many are the expected ones (Severity 019-025) but a whole bunch of others for Low Disk, Process Blocking, Failed Logins etc.
Why is there a collation conflict with this standard Microsoft stored procedure?
Server has collation Latin1_General_CI_AS. Other system dbs have same 'except for' msdb, which has SQL_Latin1_General_CP1_CI_AS.
Collation is a mess on the instance - half the databases have one collation, half have the other.
Should msdb have same collation as other system dbs? Can I/should I change its collation?