SQL Server fix data collection error -1073741819 did not generate any output

env: Windows Server 2016
        SQL Server 2016 SP2

Data Collection是一項簡易的內建監控程式，可以透過SSMS設定並且將資料存放到遠端的SQL Server。但是對於此功能重度依賴的單位，一旦此功能出現無法完整運作的狀況，就會立即出現極度的不便的情形。

遇到以下的訊息，造成收集"Server Active"的功能無法運作。
message:
Executed as user: XXXX\XXXX. The step did not generate any output.  Process Exit Code -1073741819.

這個狀況僅只有少部分SQL Server有此狀況。

1.確認Data Collection排程失敗狀況
message:
Executed as user: XXXX\XXXX. The step did not generate any output.  Process Exit Code -1073741819.  .  NOTE: The step was retried the requested number of times (3) without succeeding.  The step failed.

2.嘗試清除Data Collection並且重啟Data Collection
結果不成功

3.檢查Data Collection的SSIS執行狀況
可以利用SSMS並且選擇Integration service來找到相關的Package.
與正常運作Data Collection的SQL Server比較後沒有不同。

4.利用SQL Profiler紀錄Data Collection執行語法語過程

SERVER1:Data Collection失敗

--無資料

exec sp_executesql N'[msdb].[dbo].[sp_ssis_getpackage] @P1, @P2',N'@P1 nvarchar(4000),@P2 uniqueidentifier',N'TSQLQuery_TW-UGSSTAGDB01_MSSQL13_MSSQLSERVER_{49268954-4FD4-4EB6-AA04-CD59D9BB5714}_3_Collect','39163C42-602B-42C9-B4F7-1843614F9625'

--有資料

exec sp_executesql N'[msdb].[dbo].[sp_ssis_getfolder] @P1, @P2',N'@P1 nvarchar(4000),@P2 uniqueidentifier',N'Generated','8877FE4B-A938-4A51-84B9-C5BDAD74B0AD'

--有資料

exec sp_executesql N'[msdb].[dbo].[sp_ssis_getfolder] @P1, @P2',N'@P1 nvarchar(4000),@P2 uniqueidentifier',N'Data Collector','00000000-0000-0000-0000-000000000000'

--有資料

declare @p5 nvarchar(max)

set @p5=N'4C134E66-FB60-4811-BA8F-3A1689695D22'

declare @p6 nvarchar(max)

set @p6=N'4AF3DD56-37D5-47E4-8C53-B225E19C2740'

declare @p7 nvarchar(max)

set @p7=N'TSQLQuery_TW-UGSSTAGDB01_MSSQL13_MSSQLSERVER_{49268954-4FD4-4EB6-AA04-CD59D9BB5714}_3_Collect'

declare @p8 nvarchar(max)

set @p8=N'TSQLQuery_TW-UGSSTAGDB01_MSSQL13_MSSQLSERVER_{49268954-4FD4-4EB6-AA04-CD59D9BB5714}_3_Upload'

exec sp_executesql N'EXEC dbo.sp_syscollector_get_tsql_query_collector_package_ids @P1, @P2, @P3 OUTPUT, @P4 OUTPUT, @P5 OUTPUT, @P6 OUTPUT',N'@P1 nvarchar(38),@P2 int,@P3 nvarchar(max) OUTPUT,@P4 nvarchar(max) OUTPUT,@P5 nvarchar(max) OUTPUT,@P6 nvarchar(max) OUTPUT',N'{49268954-4FD4-4EB6-AA04-CD59D9BB5714}',3,@p5 output,@p6 output,@p7 output,@p8 output

select @p5, @p6, @p7, @p8

SERVER2:Data Collection正常運作

--有資料

exec sp_executesql N'[msdb].[dbo].[sp_ssis_getpackage] @P1, @P2',N'@P1 nvarchar(4000),@P2 uniqueidentifier',N'TSQLQuery_TW-UGSQADB04_MSSQL13_MSSQLSERVER_{49268954-4FD4-4EB6-AA04-CD59D9BB5714}_3_Collect','39163C42-602B-42C9-B4F7-1843614F9625'

--有資料

exec sp_executesql N'[msdb].[dbo].[sp_ssis_getfolder] @P1, @P2',N'@P1 nvarchar(4000),@P2 uniqueidentifier',N'Generated','8877FE4B-A938-4A51-84B9-C5BDAD74B0AD'

--有資料

exec sp_executesql N'[msdb].[dbo].[sp_ssis_getfolder] @P1, @P2',N'@P1 nvarchar(4000),@P2 uniqueidentifier',N'Data Collector','00000000-0000-0000-0000-000000000000'

--有資料

declare @p5 nvarchar(max)

set @p5=N'F96A230F-00EA-4A54-86DC-0E1D0318B1A1'

declare @p6 nvarchar(max)

set @p6=N'C8051C44-55BC-42AD-A824-72C87EDEE5CF'

declare @p7 nvarchar(max)

set @p7=N'TSQLQuery_TW-UGSQADB04_MSSQL13_MSSQLSERVER_{49268954-4FD4-4EB6-AA04-CD59D9BB5714}_3_Collect'

declare @p8 nvarchar(max)

set @p8=N'TSQLQuery_TW-UGSQADB04_MSSQL13_MSSQLSERVER_{49268954-4FD4-4EB6-AA04-CD59D9BB5714}_3_Upload'

exec sp_executesql N'EXEC dbo.sp_syscollector_get_tsql_query_collector_package_ids @P1, @P2, @P3 OUTPUT, @P4 OUTPUT, @P5 OUTPUT, @P6 OUTPUT',N'@P1 nvarchar(38),@P2 int,@P3 nvarchar(max) OUTPUT,@P4 nvarchar(max) OUTPUT,@P5 nvarchar(max) OUTPUT,@P6 nvarchar(max) OUTPUT',N'{49268954-4FD4-4EB6-AA04-CD59D9BB5714}',3,@p5 output,@p6 output,@p7 output,@p8 output

select @p5, @p6, @p7, @p8

差異在第一句有無產生資料
exec sp_executesql N'[msdb].[dbo].[sp_ssis_getpackage] @P1, @P2',N'@P1 nvarchar(4000),@P2 uniqueidentifier',N'TSQLQuery_TW-UGSSTAGDB01_MSSQL13_MSSQLSERVER_{49268954-4FD4-4EB6-AA04-CD59D9BB5714}_3_Collect','39163C42-602B-42C9-B4F7-1843614F9625'


5.嘗試重新設定效能計數器
結果不成功

Fix PerfNet

1. Rebuilding the counters:
     cd c:\windows\system32
     lodctr /R
     cd c:\windows\sysWOW64
     lodctr /R

C:\Windows\system32>cd c:\windows\system32

C:\Windows\system32>lodctr /R

Info: Successfully rebuilt performance counter setting from system backup store

C:\Windows\system32>cd c:\windows\sysWOW64

c:\Windows\SysWOW64>lodctr /R

Info: Successfully rebuilt performance counter setting from system backup store

c:\Windows\SysWOW64>

2. Resyncing the counters with Windows Management Instrumentation (WMI):
     WINMGMT.EXE /RESYNCPERF

c:\Windows\SysWOW64>WINMGMT.EXE /RESYNCPERF

c:\Windows\SysWOW64>

6.調整registry key
調整:
HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Session Manager\SubSystems

將SharedSection的第三個值從768調整為1536，重啟Windows server。
重啟Data Collection排程，成功運作。


from

%SystemRoot%\system32\csrss.exe ObjectDirectory=\Windows SharedSection=1024,20480,768 Windows=On SubSystemType=Windows ServerDll=basesrv,1 ServerDll=winsrv:UserServerDllInitialization,3 ServerDll=sxssrv,4 ProfileControl=Off MaxRequestThreads=16

to

%SystemRoot%\system32\csrss.exe ObjectDirectory=\Windows SharedSection=1024,20480,1536 Windows=On SubSystemType=Windows ServerDll=basesrv,1 ServerDll=winsrv:UserServerDllInitialization,3 ServerDll=sxssrv,4 ProfileControl=Off MaxRequestThreads=16


7.若SharedSection調整為1536沒有功用，可以調整為2048試試。