大网数据广告活动
数据库与应用安全

MSDB database grows on SQL Server

· 52CE 我爱测

This article provides workarounds for the problem where MSDB database grows on SQL Server with supplementary character collations.

Original product version:   SQL Server 12 and later versions

Symptom

Consider the following scenario:

  • You installed an instance with a collation using supplementary characters for system database in SQL Server.
  • You have configured Management Data Warehouse (MDW) and Data Collection.

In this scenario, when the SQL Server Integration Services (SSIS) packages created by the Data Collection run, you will find the MSDB database grows unexpectedly.

Note: That kind of collation name ends with _SC, for example, Latin1_General_100_CI_AS_SC.

Every 10 seconds, the following 11 warning messages are inserted into the msdb.dbo.sysssislog system table:

Truncation may occur due to retrieving data from database column "task_state" with a length of 20 to data flow column "task_state" with a length of 10.

Truncation may occur due to retrieving data from database column "request_status" with a length of 30 to data flow column "request_status" with a length of 15.

Truncation may occur due to retrieving data from database column "session_status" with a length of 30 to data flow column "session_status" with a length of 15.

Truncation may occur due to retrieving data from database column "host_name" with a length of 40 to data flow column "host_name" with a length of 20.

Truncation may occur due to retrieving data from database column "program_name" with a length of 100 to data flow column "program_name" with a length of 50.

Truncation may occur due to retrieving data from database column "login_name" with a length of 60 to data flow column "login_name" with a length of 30.

Truncation may occur due to retrieving data from database column "wait_type" with a length of 60 to data flow column "wait_type" with a length of 45.

Truncation may occur due to retrieving data from database column "last_wait_type" with a length of 60 to data flow column "last_wait_type" with a length of 45.

Truncation may occur due to retrieving data from database column "wait_resource" with a length of 100 to data flow column "wait_resource" with a length of 50.

Truncation may occur due to retrieving data from database column "resource_description" with a length of 280 to data flow column "resource_description" with a length of 140.

The external columns for ODS - Get snapshot of dm_exec_requests are out of synchronization with the data source columns. The external column "task_state" needs to be updated. The external column "request_status" needs to be updated. The external column "session_status" needs to be updated. The external column "host_name" needs to be updated. The external column "program_name" needs to be updated. The external column "login_name" needs to be updated. The external column "wait_type" needs to be updated. The external column "last_wait_type" needs to be updated. The external column "wait_resource" needs to be updated. The external column "resource_description" needs to be updated.

As a result, the msdb.dbo.sysssislog table grows one page (8 KB) every 10 seconds. The impact on the disk space is:

  • 2.8 MB per hour (8 KB x 6 x 60)
  • 67.5 MB per day (8 KB x 6 x 60 x 24)
  • 1.97 GB per month (8 KB x 6 x 60 x 24 x 30)

Cause

The collations using supplementary characters will change the size of some Dynamic Management View (DMV) columns. The Data Collector tool captures the content of some DMVs, for example sys.dm_exec_requests, and adds the results into the MDW database by using SSIS packages. In these SSIS packages, column sizes are predefined based on the size of columns for collations without using supplementary characters. When the packages run, a warning message is returned for each column whose size is bigger than the predefined size and is added to the msdb.dbo.sysssislog table.

Note: These warning messages don't affect the insertion of actual data from the DMV into the data collection table.

More information

Data collection process collects the metadata for the first result set when calling sp_syscollector_snapshot_dm_exec_requests. The following sql script is an example:

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

This call returns different results depending on the collation, using supplementary characters or not:

  • Instance with a collation that doesn't use supplementary characters:

    [!NOTE] For more clarity, only affected rows and columns are shown.

    Column ordinal name Is nullable System type_id System type_name Max length Collation name
    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 with a collation that uses supplementary characters:

    [!NOTE] For more clarity, only affected rows and columns are shown.

    Column ordinal name Is nullable System type_id System type_name Max length Collation name
    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

Both instances show the difference on some columns. For example, the wait_type column has a system type name of nvarchar(45) with a max length of 90 on a standard collation. However, collations using supplementary characters have a system type name of nvarchar(60) with a max length of 120. The size difference causes the warning messages that are logged by the SSIS package.

Workaround

You can use one of the following workarounds:

Important: If you use workaround 1 and workaround 2, any existing logins, jobs, or packages will be lost. These workarounds should only be used in a non-production environment.

Workaround 1

Uninstall and reinstall the SQL Server instance with a collation without using supplementary characters.

Workaround 2

Rebuild the system databases instance with a collation without using supplementary characters.

Workaround 3

Create a job to remove the unnecessary warnings from the msdb.dbo.sysssislog table. In the following example, the job is scheduled to run every hour. You can define a different schedule as needed by changing the @freq_subday_interval value.

For example, you can change the @freq_subday_interval value from 1 to 2 to run the job every two hours.

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

Source: Microsoft Learn / IT Pro troubleshooting by Microsoft and contributors (upstream author metadata: aartig13). Documentation licensed CC BY 4.0. Code samples are covered by the MIT License. Converted from Markdown, with links normalized and embedded media omitted.

资料来源与适用范围

资料来源
Microsoft Learn / IT Pro troubleshooting
适用范围
See source applicability · Source date 12/28/2020
内容语言
中文;命令、路径和错误标识保留技术原文
本地收录日期
2026-11-15
授权说明
CC BY 4.0 (documentation), MIT (code samples)

正文与所需资源由本站本地存储。查看来源及更新记录 ↗

请按适用版本和实际日志确认原因;本站没有对每一种系统或设备逐项执行文中的操作。