MSDB veritabanı SQL Server'da ek karakter harmanlamalarıyla büyür

2024-12-14

Özgün ürün sürümü: SQL Server 12 ve sonraki sürümleri

Belirti

Şu senaryoyu göz önünde bulundurun:

SQL Server'da sistem veritabanı için ek karakterler kullanarak harmanlama içeren bir örnek yüklemişsiniz.
Yönetim Veri Ambarı (MDW) ve Veri Toplama'yi yapılandırdığınız.

Bu senaryoda, Veri Toplama tarafından oluşturulan SQL Server Integration Services (SSIS) paketleri çalıştırıldığında MSDB veritabanının beklenmedik şekilde büyüdüğünü göreceksiniz.

Not

Bu tür bir harmanlama adı _SC ile biter; örneğin, Latin1_General_100_CI_AS_SC.

Her 10 saniyede bir, sistem tablosuna msdb.dbo.sysssislog aşağıdaki 11 uyarı iletisi eklenir:

Kesme işlemi, uzunluğu 20 olan "task_state" veritabanı sütunundan 10 uzunluğa sahip "task_state" veri akışı sütununa veri alınmasından kaynaklanabilir.

Kesme işlemi, uzunluğu 30 olan "request_status" veritabanı sütunundan 15 uzunluğundaki "request_status" veri akışı sütununa veri alınmasından kaynaklanabilir.

Kesme işlemi, uzunluğu 30 olan "session_status" veritabanı sütunundan 15 uzunluğundaki "session_status" veri akışı sütununa veri alınmasından kaynaklanabilir.

Kesme işlemi, uzunluğu 40 olan "host_name" veritabanı sütunundan 20 uzunluğundaki "host_name" veri akışı sütunundan veri alınmasından kaynaklanabilir.

Kesme işlemi, uzunluğu 100 olan "program_name" veritabanı sütunundan 50 uzunluğa sahip "program_name" veri akışı sütununa veri alınmasından kaynaklanabilir.

Kesme işlemi, uzunluğu 60 olan "login_name" veritabanı sütunundan 30 uzunluğundaki "login_name" veri akışı sütunundan veri alınmasından kaynaklanabilir.

Kesme işlemi, uzunluğu 60 olan "wait_type" veritabanı sütunundan 45 uzunluğundaki "wait_type" veri akışı sütunundan veri alınmasından kaynaklanabilir.

Kesme işlemi, uzunluğu 60 olan "last_wait_type" veritabanı sütunundan 45 uzunluğundaki "last_wait_type" veri akışı sütununa veri alınmasından kaynaklanabilir.

Kesme işlemi, uzunluğu 100 olan "wait_resource" veritabanı sütunundan 50 uzunluğa sahip "wait_resource" veri akışı sütununa veri alınmasından kaynaklanabilir.

Kesme işlemi, uzunluğu 280 olan "resource_description" veritabanı sütunundan 140 uzunluğundaki "resource_description" veri akışı sütununa veri alınmasından kaynaklanabilir.

ODS için dış sütunlar - dm_exec_requests anlık görüntüsünü alma, veri kaynağı sütunlarıyla eşitlemenin dışındadır. "task_state" dış sütununun güncelleştirilmesi gerekir. "request_status" dış sütununun güncelleştirilmesi gerekir. "session_status" dış sütununun güncelleştirilmesi gerekir. "host_name" dış sütununun güncelleştirilmesi gerekir. "program_name" dış sütununun güncelleştirilmesi gerekir. "login_name" dış sütununun güncelleştirilmesi gerekir. "wait_type" dış sütununun güncelleştirilmesi gerekir. "last_wait_type" dış sütununun güncelleştirilmesi gerekir. "wait_resource" dış sütununun güncelleştirilmesi gerekir. "resource_description" dış sütununun güncelleştirilmesi gerekir.

Sonuç olarak, msdb.dbo.sysssislog tablo her 10 saniyede bir sayfa (8 KB) büyür. Disk alanı üzerindeki etkisi:

Saatte 2,8 MB (8 KB x 6 x 60)
Günde 67,5 MB (8 KB x 6 x 60 x 24)
Ayda 1,97 GB (8 KB x 6 x 60 x 24 x 30)

Neden

Tamamlayıcı karakterler kullanan harmanlamalar, bazı Dinamik Yönetim Görünümü (DMV) sütunlarının boyutunu değiştirir. Veri Toplayıcı aracı, gibi sys.dm_exec_requestsbazı DMV'lerin içeriğini yakalar ve SSIS paketlerini kullanarak sonuçları MDW veritabanına ekler. Bu SSIS paketlerinde sütun boyutları, tamamlayıcı karakterler kullanılmadan harmanlama sütunlarının boyutuna göre önceden tanımlanmıştır. Paketler çalıştırıldığında, boyutu önceden tanımlanmış boyuttan büyük olan ve tabloya msdb.dbo.sysssislog eklenen her sütun için bir uyarı iletisi döndürülür.

Not

Bu uyarı iletileri, DMV'den veri toplama tablosuna gerçek verilerin eklenmesini etkilemez.

Daha Fazla Bilgi

Veri toplama işlemi çağrılırken sp_syscollector_snapshot_dm_exec_requestsilk sonuç kümesinin meta verilerini toplar. Aşağıdaki sql betiği bir örnektir:

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

Bu çağrı, tamamlayıcı karakterler kullanıp kullanmamaya bağlı olarak harmanlama işlemine bağlı olarak farklı sonuçlar döndürür:

Tamamlayıcı karakterler kullanmayan harmanlama içeren örnek:

Not

Daha netlik sağlamak için yalnızca etkilenen satırlar ve sütunlar gösterilir.

Sütun sıralı	Adı	Boş değer atanabilir	Sistem type_id	Sistem type_name	Maksimum uzunluk	Harmanlama adı
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

Tamamlayıcı karakterler kullanan harmanlama içeren örnek:

Not

Daha netlik sağlamak için yalnızca etkilenen satırlar ve sütunlar gösterilir.

Sütun sıralı	Adı	Boş değer atanabilir	Sistem type_id	Sistem type_name	Maksimum uzunluk	Harmanlama adı
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

Her iki örnek de bazı sütunlardaki farkı gösterir. Örneğin, sütun standart wait_type harmanlamada maksimum uzunluğu 90 olan nvarchar(45) sistem türüne sahiptir. Ancak, tamamlayıcı karakterler kullanan harmanlamaların sistem türü adı nvarchar(60) ve uzunluk üst sınırı 120'dir. Boyut farkı, SSIS paketi tarafından günlüğe kaydedilen uyarı iletilerine neden olur.

Geçici çözüm

Aşağıdaki geçici çözümlerden birini kullanabilirsiniz:

Önemli

Geçici çözüm 1 ve geçici çözüm 2'yi kullanırsanız, mevcut oturum açma bilgileri, işler veya paketler kaybolur. Bu geçici çözümler yalnızca üretim dışı bir ortamda kullanılmalıdır.

Geçici çözüm 1

Tamamlayıcı karakterler kullanmadan SQL Server örneğini bir harmanlamayla kaldırın ve yeniden yükleyin.

Geçici çözüm 2

Tamamlayıcı karakterler kullanmadan bir harmanlama ile sistem veritabanları örneğini yeniden oluşturun.

Geçici çözüm 3

Gereksiz uyarıları msdb.dbo.sysssislog tablodan kaldırmak için bir iş oluşturun. Aşağıdaki örnekte, iş saatte bir çalışacak şekilde zamanlanmıştır. Değeri değiştirerek @freq_subday_interval gerektiğinde farklı bir zamanlama tanımlayabilirsiniz.

Örneğin, işi iki saatte bir çalıştırmak için 1 olan değeri 2 olarak değiştirebilirsiniz@freq_subday_interval.

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

Aracılığıyla paylaş