O banco de dados MSDB cresce no SQL Server com agrupamentos de caracteres suplementares

Versão original do produto: SQL Server 12 e versões posteriores

Sintoma

Considere o cenário a seguir.

Você instalou uma instância com uma ordenação usando caracteres suplementares para o banco de dados do sistema no SQL Server.
Você configurou o Management Data Warehouse (MDW) e a Coleta de Dados.

Nesse cenário, quando os pacotes do SQL Server Integration Services (SSIS) criados pela Coleta de Dados forem executados, você descobrirá que o banco de dados MSDB cresce inesperadamente.

Observação

Esse tipo de nome de agrupamento termina com _SC, por exemplo, Latin1_General_100_CI_AS_SC.

A cada 10 segundos, as 11 mensagens de aviso a seguir são inseridas na tabela do msdb.dbo.sysssislog sistema:

O truncamento pode ocorrer devido à recuperação de dados da coluna de banco de dados "task_state" com um comprimento de 20 para a coluna de fluxo de dados "task_state" com um comprimento de 10.

O truncamento pode ocorrer devido à recuperação de dados da coluna de banco de dados "request_status" com um comprimento de 30 para a coluna de fluxo de dados "request_status" com um comprimento de 15.

O truncamento pode ocorrer devido à recuperação de dados da coluna de banco de dados "session_status" com um comprimento de 30 para a coluna de fluxo de dados "session_status" com um comprimento de 15.

O truncamento pode ocorrer devido à recuperação de dados da coluna de banco de dados "host_name" com um comprimento de 40 para a coluna de fluxo de dados "host_name" com um comprimento de 20.

O truncamento pode ocorrer devido à recuperação de dados da coluna de banco de dados "program_name" com um comprimento de 100 para a coluna de fluxo de dados "program_name" com um comprimento de 50.

O truncamento pode ocorrer devido à recuperação de dados da coluna de banco de dados "login_name" com um comprimento de 60 para a coluna de fluxo de dados "login_name" com um comprimento de 30.

O truncamento pode ocorrer devido à recuperação de dados da coluna de banco de dados "wait_type" com um comprimento de 60 para a coluna de fluxo de dados "wait_type" com um comprimento de 45.

O truncamento pode ocorrer devido à recuperação de dados da coluna de banco de dados "last_wait_type" com um comprimento de 60 para a coluna de fluxo de dados "last_wait_type" com um comprimento de 45.

O truncamento pode ocorrer devido à recuperação de dados da coluna de banco de dados "wait_resource" com um comprimento de 100 para a coluna de fluxo de dados "wait_resource" com um comprimento de 50.

O truncamento pode ocorrer devido à recuperação de dados da coluna de banco de dados "resource_description" com um comprimento de 280 para a coluna de fluxo de dados "resource_description" com um comprimento de 140.

As colunas externas do ODS – Obter instantâneo de dm_exec_requests estão fora de sincronia com as colunas da fonte de dados. A coluna externa "task_state" precisa ser atualizada. A coluna externa "request_status" precisa ser atualizada. A coluna externa "session_status" precisa ser atualizada. A coluna externa "host_name" precisa ser atualizada. A coluna externa "program_name" precisa ser atualizada. A coluna externa "login_name" precisa ser atualizada. A coluna externa "wait_type" precisa ser atualizada. A coluna externa "last_wait_type" precisa ser atualizada. A coluna externa "wait_resource" precisa ser atualizada. A coluna externa "resource_description" precisa ser atualizada.

Como resultado, a msdb.dbo.sysssislog tabela aumenta uma página (8 KB) a cada 10 segundos. O impacto no espaço em disco é:

2,8 MB por hora (8 KB x 6 x 60)
67,5 MB por dia (8 KB x 6 x 60 x 24)
1,97 GB por mês (8 KB x 6 x 60 x 24 x 30)

Causa

Os agrupamentos que usam caracteres suplementares alterarão o tamanho de algumas colunas de DMV (Modo de Exibição de Gerenciamento Dinâmico). A ferramenta Coletor de Dados captura o conteúdo de algumas DMVs, por exemplo sys.dm_exec_requests, e adiciona os resultados ao banco de dados MDW usando pacotes SSIS. Nesses pacotes SSIS, os tamanhos das colunas são predefinidos com base no tamanho das colunas para agrupamentos sem o uso de caracteres suplementares. Quando os pacotes são executados, uma mensagem de aviso é retornada para cada coluna cujo tamanho é maior que o tamanho predefinido e é adicionada à msdb.dbo.sysssislog tabela.

Observação

Essas mensagens de aviso não afetam a inserção de dados reais do DMV na tabela de coleta de dados.

Mais informações

O processo de coleta de dados coleta os metadados para o primeiro conjunto de resultados ao chamar sp_syscollector_snapshot_dm_exec_requests. O script sql a seguir é um exemplo:

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

Essa chamada retorna resultados diferentes dependendo da ordenação, usando caracteres suplementares ou não:

Instância com um agrupamento que não usa caracteres suplementares:

Observação

Para maior clareza, somente as linhas e colunas afetadas são mostradas.

Ordinal de coluna	name	Permite valor nulo	Sistema type_id	type_name do sistema	Comprimento máximo	Nome da ordenação
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	NULO
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

Instância com um agrupamento que usa caracteres suplementares:

Observação

Para maior clareza, somente as linhas e colunas afetadas são mostradas.

Ordinal de coluna	name	Permite valor nulo	Sistema type_id	type_name do sistema	Comprimento máximo	Nome da ordenação
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

Ambas as instâncias mostram a diferença em algumas colunas. Por exemplo, a wait_type coluna tem um nome de tipo de sistema de nvarchar(45) com um comprimento máximo de 90 em uma ordenação padrão. No entanto, os agrupamentos que usam caracteres suplementares têm um nome de tipo de sistema de nvarchar(60) com um comprimento máximo de 120. A diferença de tamanho faz com que as mensagens de aviso registradas pelo pacote SSIS.

Solução alternativa

Você pode usar uma das seguintes soluções alternativas:

Importante

Se você usar a solução alternativa 1 e a solução alternativa 2, todos os logons, trabalhos ou pacotes existentes serão perdidos. Essas soluções alternativas só devem ser usadas em um ambiente de não produção.

Solução alternativa 1

Desinstale e reinstale a instância do SQL Server com uma ordenação sem usar caracteres suplementares.

Solução alternativa 2

Recompile a instância de bancos de dados do sistema com uma ordenação sem usar caracteres suplementares.

Solução alternativa 3

Crie um trabalho para remover os avisos desnecessários da msdb.dbo.sysssislog tabela. No exemplo a seguir, o trabalho está agendado para ser executado a cada hora. Você pode definir uma programação diferente, conforme necessário, alterando o @freq_subday_interval valor.

Por exemplo, você pode alterar o @freq_subday_interval valor de 1 para 2 para executar o trabalho a cada duas horas.

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

Comentários

Esta página foi útil?

Last updated on 2024-12-14

Compartilhar via

O banco de dados MSDB cresce no SQL Server com agrupamentos de caracteres suplementares

Sintoma

Causa

Mais informações

Solução alternativa

Solução alternativa 1

Solução alternativa 2

Solução alternativa 3

Comentários

Recursos adicionais