La base de données MSDB augmente sur SQL Server avec des classements de caractères supplémentaires

2024-12-14

Version du produit d’origine : SQL Server 12 et versions ultérieures

Symptôme

Examinez le cas suivant :

Vous avez installé une instance avec un classement à l’aide de caractères supplémentaires pour la base de données système dans SQL Server.
Vous avez configuré Management Data Warehouse (MDW) et la collecte de données.

Dans ce scénario, lorsque les packages SQL Server Integration Services (SSIS) créés par l’exécution de la collecte de données, vous trouverez que la base de données MSDB augmente de façon inattendue.

Note

Ce type de nom de classement se termine par _SC, par exemple Latin1_General_100_CI_AS_SC.

Toutes les 10 secondes, les 11 messages d’avertissement suivants sont insérés dans la msdb.dbo.sysssislog table système :

La troncation peut se produire en raison de la récupération des données de la colonne de base de données « task_state » avec une longueur de 20 à la colonne de flux de données « task_state » avec une longueur de 10.

La troncation peut se produire en raison de la récupération des données de la colonne de base de données « request_status » avec une longueur de 30 à la colonne de flux de données « request_status » avec une longueur de 15.

La troncation peut se produire en raison de la récupération des données de la colonne de base de données « session_status » avec une longueur de 30 à la colonne de flux de données « session_status » avec une longueur de 15.

La troncation peut se produire en raison de la récupération des données de la colonne de base de données « host_name » avec une longueur de 40 à la colonne de flux de données « host_name » avec une longueur de 20.

La troncation peut se produire en raison de la récupération des données de la colonne de base de données « program_name » avec une longueur de 100 à la colonne de flux de données « program_name » avec une longueur de 50.

La troncation peut se produire en raison de la récupération des données de la colonne de base de données « login_name » avec une longueur de 60 à la colonne de flux de données « login_name » avec une longueur de 30.

La troncation peut se produire en raison de la récupération des données de la colonne de base de données « wait_type » avec une longueur de 60 à la colonne de flux de données « wait_type » avec une longueur de 45.

La troncation peut se produire en raison de la récupération des données de la colonne de base de données « last_wait_type » avec une longueur de 60 à la colonne de flux de données « last_wait_type » avec une longueur de 45.

La troncation peut se produire en raison de la récupération des données de la colonne de base de données « wait_resource » avec une longueur de 100 à la colonne de flux de données « wait_resource » avec une longueur de 50.

La troncation peut se produire en raison de la récupération des données de la colonne de base de données « resource_description » avec une longueur de 280 à la colonne de flux de données « resource_description » avec une longueur de 140.

Les colonnes externes pour ODS - Obtenir un instantané de dm_exec_requests ne sont pas synchronisées avec les colonnes de source de données. La colonne externe « task_state » doit être mise à jour. La colonne externe « request_status » doit être mise à jour. La colonne externe « session_status » doit être mise à jour. La colonne externe « host_name » doit être mise à jour. La colonne externe « program_name » doit être mise à jour. La colonne externe « login_name » doit être mise à jour. La colonne externe « wait_type » doit être mise à jour. La colonne externe « last_wait_type » doit être mise à jour. La colonne externe « wait_resource » doit être mise à jour. La colonne externe « resource_description » doit être mise à jour.

Par conséquent, la msdb.dbo.sysssislog table augmente d’une page (8 Ko) toutes les 10 secondes. L’impact sur l’espace disque est :

2,8 Mo par heure (8 Ko x 6 x 60)
67,5 Mo par jour (8 Ko x 6 x 60 x 24)
1,97 Go par mois (8 Ko x 60 x 60 x 24 x 30)

Cause

Les classements utilisant des caractères supplémentaires modifient la taille de certaines colonnes DMV (Dynamic Management View). L’outil Collecteur de données capture le contenu de certaines DMV, par exemple sys.dm_exec_requests, et ajoute les résultats dans la base de données MDW à l’aide de packages SSIS. Dans ces packages SSIS, les tailles de colonne sont prédéfinies en fonction de la taille des colonnes pour les classements sans utiliser de caractères supplémentaires. Lorsque les packages s’exécutent, un message d’avertissement est retourné pour chaque colonne dont la taille est supérieure à la taille prédéfinie et ajoutée à la msdb.dbo.sysssislog table.

Note

Ces messages d’avertissement n’affectent pas l’insertion de données réelles de la vue dynamique dans la table de collecte de données.

Plus d’informations

Le processus de collecte de données collecte les métadonnées du premier jeu de résultats lors de l’appel sp_syscollector_snapshot_dm_exec_requests. Le script sql suivant est un exemple :

exec [sys].sp_describe_first_result_set N'EXEC [msdb].[dbo].[sp_syscollector_snapshot_dm_exec_requests] 5',NULL,1

Cet appel retourne des résultats différents en fonction du classement, à l’aide de caractères supplémentaires ou non :

Instance avec un classement qui n’utilise pas de caractères supplémentaires :

Note

Pour plus de clarté, seules les lignes et colonnes affectées sont affichées.

Numéro de colonne	nom	Est de type Null	Type_id système	Type_name système	Longueur maximale	Nom du classement
11	task_state	1	231	nvarchar(10)	20	SQL_Latin1_General_CP1_CI_AS
12	request_status	1	231	nvarchar(15)	30	SQL_Latin1_General_CP1_CI_AS
13	session_status	1	231	nvarchar(15)	30	SQL_Latin1_General_CP1_CI_AS
...
17	host_name	1	231	nvarchar(20)	40	SQL_Latin1_General_CP1_CI_AS
18	program_name	1	231	nvarchar(50)	100	SQL_Latin1_General_CP1_CI_AS
19	login_name	1	231	nvarchar(30)	60	SQL_Latin1_General_CP1_CI_AS
20	wait_type	1	231	nvarchar(45)	90	SQL_Latin1_General_CP1_CI_AS
21	last_wait_type	1	231	nvarchar(45)	90	SQL_Latin1_General_CP1_CI_AS
22	wait_duration_ms	0	127	bigint	8	NULL
23	wait_resource	1	231	nvarchar(50)	100	SQL_Latin1_General_CP1_CI_AS
24	resource_description	1	231	nvarchar(140)	280	SQL_Latin1_General_CP1_CI_AS

Instance avec un classement qui utilise des caractères supplémentaires :

Note

Pour plus de clarté, seules les lignes et colonnes affectées sont affichées.

Numéro de colonne	nom	Est de type Null	Type_id système	Type_name système	Longueur maximale	Nom du classement
11	task_state	1	231	nvarchar(20)	40	SQL_Latin1_General_CP1_CI_AS
12	request_status	1	231	nvarchar(30)	60	SQL_Latin1_General_CP1_CI_AS
13	session_status	1	231	nvarchar(30)	60	SQL_Latin1_General_CP1_CI_AS
...
17	host_name	1	231	nvarchar(20)	40	SQL_Latin1_General_CP1_CI_AS
18	program_name	1	231	nvarchar(100)	200	SQL_Latin1_General_CP1_CI_AS
19	login_name	1	231	nvarchar(60)	120	SQL_Latin1_General_CP1_CI_AS
20	wait_type	1	231	nvarchar(60)	120	SQL_Latin1_General_CP1_CI_AS
21	last_wait_type	1	231	nvarchar(60)	120	SQL_Latin1_General_CP1_CI_AS
23	wait_resource	1	231	nvarchar(50)	100	SQL_Latin1_General_CP1_CI_AS
24	resource_description	1	231	nvarchar(280)	560	SQL_Latin1_General_CP1_CI_AS

Les deux instances montrent la différence sur certaines colonnes. Par exemple, la wait_type colonne a un nom de type système de nvarchar(45) avec une longueur maximale de 90 sur un classement standard. Toutefois, les classements utilisant des caractères supplémentaires ont un nom de type système nvarchar (60) avec une longueur maximale de 120. La différence de taille provoque les messages d’avertissement enregistrés par le package SSIS.

Solution de contournement

Vous pouvez utiliser l’une des solutions de contournement suivantes :

Important

Si vous utilisez la solution de contournement 1 et la solution 2, les connexions, travaux ou packages existants sont perdus. Ces solutions de contournement ne doivent être utilisées que dans un environnement hors production.

Solution de contournement 1

Désinstallez et réinstallez l’instance SQL Server avec un classement sans utiliser de caractères supplémentaires.

Solution de contournement 2

Régénérez l’instance de bases de données système avec un classement sans utiliser de caractères supplémentaires.

Solution de contournement 3

Créez un travail pour supprimer les avertissements inutiles de la msdb.dbo.sysssislog table. Dans l’exemple suivant, le travail est planifié pour s’exécuter toutes les heures. Vous pouvez définir une planification différente en fonction des besoins en modifiant la @freq_subday_interval valeur.

Par exemple, vous pouvez modifier la @freq_subday_interval valeur de 1 à 2 pour exécuter le travail toutes les deux heures.

USE [msdb]
GO

/****** Object:  Job [Data Collection Warnings Cleanup]    Script Date: <Datetime> ******/
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** Object:  JobCategory [[Uncategorized (Local)]]    Script Date: <Datetime> ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END

DECLARE @jobId BINARY(16)
EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'Data Collection Warnings Cleanup',
              @enabled=1,
              @notify_level_eventlog=0,
              @notify_level_email=0,
              @notify_level_netsend=0,
              @notify_level_page=0,
              @delete_level=0,
              @description=N'This jobs removes uneccessary Data Colletion Warnings from msdb.dbo.sysssislog table. These warnings are logged when system uses a supplementary characters collation.',
              @category_name=N'[Uncategorized (Local)]',
              @owner_login_name=N'WIN-BINNHII7A8S\Administrator', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object:  Step [Delete warrnings]    Script Date: <Datetime> ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Delete warrnings',
              @step_id=1,
              @cmdexec_success_code=0,
              @on_success_action=1,
              @on_success_step_id=0,
              @on_fail_action=2,
              @on_fail_step_id=0,
              @retry_attempts=0,
              @retry_interval=0,
              @os_run_priority=0, @subsystem=N'TSQL',
              @command=N'set nocount on

declare @system_collation nvarchar(128)
declare @CountWarn1 int, @CountWarn2 int
declare @InfoMsg nvarchar(200)

select @system_collation = cast(SERVERPROPERTY(''collation'') as nvarchar(128))
set @CountWarn1 = 0
set @CountWarn2 = 0

if upper(right(@system_collation,2)) = ''SC''
   begin   -- we have a supplementary characters collation we need to remove unnecessary warnings from msdb.dbo.sysssislog table

   delete
              from msdb.dbo.sysssislog
              where event = ''OnWarning''
              and source like ''Set%Master_Package_Collection''
              and message like ''Truncation may occur%''

       Set @CountWarn1 = @@ROWCOUNT

       delete
              from msdb.dbo.sysssislog
              where event = ''OnWarning''
              and source like ''Set%Master_Package_Collection''
              and message like ''The external columns for ODS - Get snapshot of%''
       Set @CountWarn2 = @@ROWCOUNT

       set @InfoMsg = ''Data Collection warning clean up job deleted ''+ltrim(str(@CountWarn1+@CountWarn2,8))+'' warnings from msdb.dbo.sysssislog table.''

       print @InfoMsg

       end
',
              @database_name=N'master',
              @flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'Run every Hour',
              @enabled=1,
              @freq_type=4,
              @freq_interval=1,
              @freq_subday_type=8,
              @freq_subday_interval=1,
              @freq_relative_interval=0,
              @freq_recurrence_factor=0,
              @active_start_date=20201224,
              @active_end_date=99991231,
              @active_start_time=0,
              @active_end_time=235959,
              @schedule_uid=N'684bccd2-7424-4011-85d6-1d81791c53fe'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
    IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:
GO

Partager via

La base de données MSDB augmente sur SQL Server avec des classements de caractères supplémentaires

Symptôme

Cause

Plus d’informations

Solution de contournement

Solution de contournement 1

Solution de contournement 2

Solution de contournement 3

Commentaires

Ressources supplémentaires