Identify the query causing collation conflict

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:

EXECUTE msdb.dbo.sp_sqlagent_get_perf_counters

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?

Server has collation Latin1_General_CI_AS. Other system dbs have same 'except for' msdb, which has SQL_Latin1_General_CP1_CI_AS

That is the source of the collation conflict error messages, which I'm sure state that the conflict is between Latin1_General_CI_AS and SQL_Latin1_General_CP1_CI_AS.

You will have to get msdb to have a collation of Latin1_General_CI_AS in order to fix this. There are a couple of ways to address this.

• You could rebuild the entire instance which would give a consistent collation to the system DBs (which kinda do need to be the same collation), though that doesn't necessarily help any of your user DBs.

• You could use the undocumented internal operation to do this, which I have documented here: Changing the Collation of the SQL Server Instance, the Databases, and All Columns in All User Databases: What Could Possibly Go Wrong?, though it is unsupported so Microsoft won't help if you run into any problems. However, with both collations being Latin1_General and Case-Insensitive, Accent-Sensitive, it's highly unlikely (though to be fair, not impossible) that you would run into any problems (as most problems would be due to changing code pages for VARCHAR data and/or changing sensitivities, neither of which are happening here). This option requires the instance be set to a collation that it isn't already set to. Since the instance is set to Latin1_General_CI_AS, you would have to pick another, which could just be SQL_Latin1_General_CP1_CI_AS. At that point, you are technically free to run it again to change everything to Latin1_General_CI_AS.

StackExchange Database Administrators Q#302139, answer score: 4