Collation check

Job details

Name:	Collation check
Platform:	Sqlserver
Category:	Availability
Description:	Checks if there is a collation conflict with temp tables and table variables.
Long description:	You may get a collation conflict when the collation of your user database does not match the collation of tempdb database. Whenever you join with the temp table (without specifying the collation for string columns) they will inherit the default collation for tempdb database causing collation conflict.
Version:	1.4
Default schedule:	30 7 7 *
Requires engine install:	Yes
Compatibility tag:	.[type=‘instance’ & databasetype=‘sqlserver’]/instance[maj_version > ‘2000′ & hasengine=‘YES’ & (engine_edition = ‘Microsoft SQL Server’	engine_edition = ‘Azure SQL Managed Instance’)]

Parameters

Name	Default value	Description
ignore databases	model	List of databases (separated by comma) which will be ignored. You can use % (percent sign) to represent wild card characters.
ignore OFFLINE databases	YES	If set to “YES“ databases with status OFFLINE will be ignored.
return status	0	Return status value (ALARM – 2, WARNING – 1, or OK – 0) when collation conflict.
ignore collations		List of collations (separated by comma) which will be ignored. You can use % (percent sign) to represent wild card characters.

Job Summary

Purpose: The purpose of this monitoring job is to check for collation conflicts between user databases and the tempdb database in Microsoft SQL Server and Azure SQL Managed Instances.
Why: This job is important because collation conflicts can lead to errors when temporal tables or table variables are used for queries combining user databases and tempdb. If these conflicts are not managed, it could result in failures in database operations and affect the integrity and performance of SQL queries.
Manual checking: You can check for potential collation conflicts manually in the database using the following SQL commands:

SELECT name, DATABASEPROPERTYEX(name, 'Collation') AS Collation, DATABASEPROPERTYEX(name,'Status') AS Status
FROM master.dbo.sysdatabases
WHERE DATABASEPROPERTYEX(name, 'Collation') != DATABASEPROPERTYEX('tempdb', 'Collation')

Job Implementation Details

This monitoring job involves creating and executing a stored procedure (dbw_collation_proc) that:
- Identifies databases that should be ignored based on the job parameters (like specific databases and offline status).
- Compares the collation of each database against the collation of the tempdb database.
- Raises an alert if a collation conflict is detected according to a predefined threshold.

Dependencies

Self-dependency: The task is dependent on the proper functioning of its own stored procedure dbw_collation_proc.
Cleanup: The procedure ensures cleanup on failure by dropping temporary tables created during the execution process.

Job Configuration Parameters

These are key adjustable parameters that influence the job’s execution but have been instructed not to be explicitly described.

Output Visualization

In the dbWatch report, the output is split into two views:
- Database Collations: This view lists all the collations used by databases categorized by the database names.
- Databases with Collation Conflicts: This view specifically captures and displays databases where the collation does not match the collation of tempdb, potentially identifying problematic databases.

Database	Issues	Number of Occurrences
Application databases	Multiplicity of collations	calculated per group
Specific databases (‘master’, ‘msdb’, ‘model’, ‘tempdb’)	Specific collation details	1

Versioning and Updates

The current version of this monitoring job is 1.4, with modifications over the previous version 1.3 including procedural changes for enhancing the detection and reporting of collation conflicts.

In summary, this job is critical for maintaining the operational compatibility and stability of SQL queries involving user databases and tempdb by monitoring and alerting on collation mismatches.