March 10, 2010 at 11:40 am
Every 20 seconds, the following row is written into the SQL Agent error log:
[298] SQLServer Error: 446, Cannot resolve collation conflict for equal to operation. [SQLSTATE 42000]
I am not able to detect what process is causing this.
There are DBs on the server which are not the same collation as the server itself.
Any ideas?
Thanks,
Paul
March 10, 2010 at 2:53 pm
How could collation of databases is different from Server collation? unless some one mentioned collation for database explicitly while creating new database.
March 11, 2010 at 1:46 am
GTR (3/10/2010)
How could collation of databases is different from Server collation?
Very easily. Restore a database from another server where the server collation was different. Attach a database with a different collation. Create a database with an explicit collation (relatively common with vendor databases)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 11, 2010 at 1:49 am
Paul, is this in the SQL error log or the SQL Agent error log? If the latter, it narrows it down to something that Agent is running across. Does MSDB have a different collation to tempDB?
You could try a profiler trace. Log the error events, T-SQL Statement starting and completing, Stored procedures Statement starting and completing. Since the error is every 20 sec, you won't have to run the trace for long and you should be able to ID the statements causing the error from that.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 11, 2010 at 6:11 am
Hi Gail,
Good call, the msdb and tempdb do not have the same collations.
This is the sproc that's getting called every 20 secs.
EXECUTE msdb.dbo.sp_sqlagent_get_perf_counters
Soooo, what are some possible solutions?
P
March 11, 2010 at 8:40 am
I'm kinda curious how that could happen. All the system databases should have the same collation. Did you restore MSDB from somewhere else?
What's the server collation and what are the collations of the following:
Model
MSDB
What are the collations of the user databases? Don't need specifics here, just how many and what collations.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 11, 2010 at 8:49 am
Hi Gail,
Yes, msdb was restored from a backup made from a server configured as
SQL Latin1_General_CP1_CI_AS.
(We upgraded the OS on the server, and did a full re-install of SQL. This box was not one I was previously responsible for, and I simply assumed it had been configured like every other SQL server here. D'oh.)
msdb SQL Latin1_General_CP1_CI_AS
model is SQL Latin1_General_CP1_CI_AI, as is the server default.
6 or so of the user DBs (which were also restored from the old machine) are SQL Latin1_General_CP1_CI_AS.
5 others are SQL Latin1_General_CP1_CI_AI.
March 11, 2010 at 8:54 am
I think the safest thing here would be to script out all your jobs, alerts and anything else in MSDB and rebuild it. There are scripts available online (on MSDN iirc).
It's generally not a good idea to copy system databases from one server to another, there's often server-specific information in them.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 11, 2010 at 8:58 am
Thanks for your help Gail.
I'll look into the msdb rebuild option.
P
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply