none
SQL Server 2008 R2 STD(64-bit)新增維護計畫失敗 "0x80004005" RRS feed

  • 問題

  • 今天遇到靈異現象,在客戶的主機上新增維護計畫失敗,看到以下所述前所未見的錯誤訊息,不知是否有其他先進知道如何解決此錯誤訊息與狀況,感恩。

     

    執行以下動作:

    1. 在 Management Studio 下以 sa 或 本機Admin 登入伺服器(local)
    2. 新增維護計畫[MaintenancePlan1]
    3. 不新增工作也不做任何修改或,直接在[設計模式]下存檔[儲存選取項目]

    發生以下錯誤訊息:

    • SaveToSQLServer 方法發現 OLE DB 錯誤碼 0x80004005(通訊連結失敗)。發出的 SQL 陳述式失敗。

    Windows Server 主機版本:

    • Windows Server 2008 R2 Standard(64-bit)
    • 未加入網域

    SQL Server 主機版本:

    • Edition:SQL Server 2008 R2 Standard(64-bit)
    • Version:10.50.1600.1

    SQL Server 服務:

    • SQL Server 啟動中(LocalSystem)
    • SSIS 啟動中(LocalSystem)
    • SSAS 啟動中(LocalSystem)
    • SSRS 啟動中(LocalSystem)
    • SQL Server Agent 啟動中(LocalSystem)
    SQL 網路組態:
    • Shared Memory(已啟用)
    • 具名管道(已啟用)
    • TCP/IP(已啟用)
    • VIA(已停用)

     

    2011年3月14日 上午 11:02

解答

所有回覆

  • Connect to sql with instance name in ssms then try again.
    2011年3月14日 下午 12:57
  • 今天抽了點時間再測試了一下,狀況依舊

     

    排列組合了使用 (local) 與 "主機名稱",sa 與 "本機 Admin" 等四種組合

    載入既有的維護計畫會出現:

    • LoadFromSQLServer 方法發現 OLE DB 錯誤碼 0x80004005(通訊連結失敗)。發出的 SQL 陳述式失敗。

    新增維護計畫會出現:

    • SaveToSQLServer 方法發現 OLE DB 錯誤碼 0x80004005(通訊連結失敗)。發出的 SQL 陳述式失敗。

    接著用 Profiler 反查一下,似乎 msdb 內有一些 sp 可以進一步反查 ssis 的狀況
    • sp_ssis_listfolders
    • sp_ssis_getfolder
    • sp_ssis_listpackages
    • sp_ssis_getpackage

    有空再繼續查看看問題在哪...

    2011年3月15日 上午 10:34
  • Did you see any error in sql agent error log? What's sql agent service account?
    2011年3月15日 下午 01:03
  • 昨天嘗試的結果:

    1. 進 SSIS 管理,可以刪掉 msdb 內維護計畫 MaintenancePlan 的封裝,但無法匯出封裝,也無法執行封裝。(不過封裝已經刪除了,也沒辦法再測試)
    2. 不知是否跟下面 SQL Server Log 出現的 Service Principal Name (SPN) 無法註冊有關係
    3. 再查兩天,沒辦法修復就只能再安裝一個 instance 湊合著用了

     

    [SQL Agent Logs:]

    • 先前成功過的:
    以下列使用者的身分執行: ServerHostName\SYSTEM。00% 完成  進度結束  進度: 2011-03-14 00:00:10.37     來源: 備份資料庫工作      執行查詢 "BACKUP DATABASE [ReportServerTempDB] TO  DISK = N'..."。: 50% 完成  進度結束  進度: 2011-03-14 00:00:10.40     來源: 備份資料庫工作      執行查詢 "declare @backupSetId as int  select @backupSetId =..."。: 100% 完成  進度結束  進度: 2011-03-14 00:00:11.87     來源: 備份資料庫工作      執行查詢 "BACKUP DATABASE [DatabaseName1] TO  DISK = N'E:\SQL..."。: 50% 完成  進度結束  進度: 2011-03-14 00:00:12.56     來源: 備份資料庫工作      執行查詢 "declare @backupSetId as int  select @backupSetId =..."。: 100% 完成  進度結束  進度: 2011-03-14 00:00:12.59     來源: 維護清除工作      執行查詢 "EXECUTE master.dbo.xp_delete_file 0,N'E:\SQL Serve..."。: 100% 完成  進度結束  DTExec: 封裝執行傳回 DTSER_SUCCESS (0)。  已啟動:  上午 12:00:00  已完成: 上午 12:00:12  經過時間:  12.09 秒.  順利執行封裝。.  步驟成功。
    • 後來失敗的:
    以下列使用者的身分執行: ServerHostName\SYSTEM。Microsoft (R) SQL Server 執行封裝公用程式  Version 10.50.1600.1 for 64-bit  Copyright (C) Microsoft Corporation 2010. All rights reserved.    已啟動:  上午 12:15:11  因為發生錯誤 0xC0014062,所以無法載入封裝 "Maintenance Plans\MaintenancePlan"。  描述: LoadFromSQLServer 方法發現 OLE DB 錯誤碼 0x80004005 (通訊連結失敗)。發出的 SQL 陳述式失敗。  來源:   已啟動:  上午 12:15:11  已完成: 上午 12:15:11  經過時間:  0.109 秒.  無法載入封裝。.  步驟失敗。



    [SQL Server Logs:]
    日期 來源 訊息
    03/17/2011 01:21:58 spid55 Using 'xpstar.dll' version '2009.100.1600' to execute extended stored procedure 'xp_enumerrorlogs'. This is an informational message only; no user action is required.
    03/17/2011 01:21:58 spid55 Attempting to load library 'xpstar.dll' into memory. This is an informational message only. No user action is required.
    03/17/2011 01:17:12 spid8s Recovery is complete. This is an informational message only. No user action is required.
    03/17/2011 01:17:12 spid15s Service Broker manager has started.
    03/17/2011 01:17:12 spid15s The Database Mirroring protocol transport is disabled or not configured.
    03/17/2011 01:17:12 spid15s The Service Broker protocol transport is disabled or not configured.
    03/17/2011 01:17:12 spid12s Starting up database 'tempdb'.
    03/17/2011 01:17:11 spid19s CHECKDB for database 'DatabaseName2' finished without errors on 2011-03-14 00:00:06.430 (local time). This is an informational message only; no user action is required.
    03/17/2011 01:17:11 spid18s CHECKDB for database 'DatabaseName1' finished without errors on 2011-03-09 00:01:37.400 (local time). This is an informational message only; no user action is required.
    03/17/2011 01:17:11 spid16s CHECKDB for database 'ReportServer' finished without errors on 2011-03-14 00:00:05.300 (local time). This is an informational message only; no user action is required.
    03/17/2011 01:17:11 spid15s CHECKDB for database 'msdb' finished without errors on 2011-03-17 01:09:57.960 (local time). This is an informational message only; no user action is required.
    03/17/2011 01:17:11 spid17s CHECKDB for database 'ReportServerTempDB' finished without errors on 2011-03-14 00:00:05.983 (local time). This is an informational message only; no user action is required.
    03/17/2011 01:17:11 spid12s Clearing tempdb database.
    03/17/2011 01:17:11 伺服器 SQL Server is now ready for client connections. This is an informational message; no user action is required.
    03/17/2011 01:17:11 伺服器 The SQL Server Network Interface library could not register the Service Principal Name (SPN) for the SQL Server service. Error: 0x54b<c/> state: 3. Failure to register an SPN may cause integrated authentication to fall back to NTLM instead of Kerberos. This is an informational message. Further action is only required if Kerberos authentication is required by authentication policies.
    03/17/2011 01:17:11 伺服器 Dedicated admin connection support was established for listening locally on port 1434.
    03/17/2011 01:17:11 伺服器 Server is listening on [ 127.0.0.1 <ipv4> 1434].
    03/17/2011 01:17:11 伺服器 Server is listening on [ ::1 <ipv6> 1434].
    03/17/2011 01:17:11 伺服器 Server local connection provider is ready to accept connection on [ \\.\pipe\sql\query ].
    03/17/2011 01:17:11 伺服器 Server local connection provider is ready to accept connection on [ \\.\pipe\SQLLocal\MSSQLSERVER ].
    03/17/2011 01:17:11 伺服器 Server is listening on [ 'any' <ipv4> 1433].
    03/17/2011 01:17:11 伺服器 Server is listening on [ 'any' <ipv6> 1433].
    03/17/2011 01:17:11 伺服器 A self-generated certificate was successfully loaded for encryption.
    03/17/2011 01:17:11 spid19s Starting up database 'DatabaseName2'.
    03/17/2011 01:17:11 spid18s Starting up database 'DatabaseName1'.
    03/17/2011 01:17:11 spid17s Starting up database 'ReportServerTempDB'.
    03/17/2011 01:17:11 spid16s Starting up database 'ReportServer'.
    03/17/2011 01:17:11 spid15s Starting up database 'msdb'.
    03/17/2011 01:17:11 spid12s CHECKDB for database 'model' finished without errors on 2011-03-17 01:09:18.810 (local time). This is an informational message only; no user action is required.
    03/17/2011 01:17:11 spid15s A new instance of the full-text filter daemon host process has been successfully started.
    03/17/2011 01:17:10 spid12s Starting up database 'model'.
    03/17/2011 01:17:10 spid8s Server name is 'EPORTFOLIO'. This is an informational message only. No user action is required.
    03/17/2011 01:17:10 spid8s The resource database build version is 10.50.1600. This is an informational message only. No user action is required.
    03/17/2011 01:17:10 spid8s Starting up database 'mssqlsystemresource'.
    03/17/2011 01:17:10 spid8s SQL Trace ID 1 was started by login "sa".
    03/17/2011 01:17:10 spid8s FILESTREAM: effective level = 0<c/> configured level = 0<c/> file system access share name = 'MSSQLSERVER'.
    03/17/2011 01:17:10 spid8s CHECKDB for database 'master' finished without errors on 2011-03-17 01:09:40.450 (local time). This is an informational message only; no user action is required.
    03/17/2011 01:17:10 spid8s Recovery is writing a checkpoint in database 'master' (1). This is an informational message only. No user action is required.
    03/17/2011 01:17:10 spid8s Starting up database 'master'.
    03/17/2011 01:17:10 伺服器 Node configuration: node 1: CPU mask: 0x00000000000000ff:0 Active CPU mask: 0x00000000000000ff:0. This message provides a description of the NUMA configuration for this computer. This is an informational message only. No user action is required.
    03/17/2011 01:17:10 伺服器 Node configuration: node 0: CPU mask: 0x000000000000ff00:0 Active CPU mask: 0x000000000000ff00:0. This message provides a description of the NUMA configuration for this computer. This is an informational message only. No user action is required.
    03/17/2011 01:17:10 伺服器 Lock partitioning is enabled.  This is an informational message only. No user action is required.
    03/17/2011 01:17:10 伺服器 Using dynamic lock allocation.  Initial allocation of 2500 Lock blocks and 5000 Lock Owner blocks per node.  This is an informational message only.  No user action is required.
    03/17/2011 01:17:10 伺服器 Detected 16 CPUs. This is an informational message; no user action is required.
    03/17/2011 01:17:10 伺服器 SQL Server is starting at normal priority base (=7). This is an informational message only. No user action is required.
    03/17/2011 01:17:10 Server Registry startup parameters: <nl/>
    03/17/2011 01:17:10 Server This instance of SQL Server last reported using a process ID of 4180 at 2011/3/17 上午 01:17:07 (local) 2011/3/16 下午 05:17:07 (UTC). This is an informational message only; no user action is required.
    03/17/2011 01:17:10 Server Logging SQL Server messages in file 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Log\ERRORLOG'.
    03/17/2011 01:17:10 Server Authentication mode is MIXED.
    03/17/2011 01:17:10 Server System Manufacturer: 'IBM'<c/> System Model: 'System x3650 M2 -[xxxxxxx]-'.
    03/17/2011 01:17:10 Server Server process ID is 3080.
    03/17/2011 01:17:10 Server All rights reserved.
    03/17/2011 01:17:10 Server (c) Microsoft Corporation.
    03/17/2011 01:17:10 Server Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (X64) <nl/>



     

    2011年3月17日 上午 02:29
  • 2011年3月17日 上午 03:16
  • 先感謝 rmiao 大的回覆,但查閱了 SPN 的線上文件後,再對照主機上的狀況後,發現與 SPN 並無直接關聯
    最後是在 Microsoft Connect 上查到相關 Issue 後,才找到線索與解決辦法,以下是相關資料的分享:

    起因:
    網路封包預設為 4096 bytes,若改變大小超過 16388 bytes,將造成 SSIS 封裝無法儲存與載入
    而內部真正是因為 SSL 與 TLS 限制每個 data fragments 不得超過 16 KB(RFC 5246 - 6.2),因此設定網路封包大小超過 16388 bytes 時,調用以下函式將產生 TLS 解封包失敗,進而造成表面 SSIS 封裝無法儲存與載入
    Microsoft.SqlServer.Dts.Runtime.SaveToSqlServer
    Microsoft.SqlServer.Dts.Runtime.LoadFromSqlServer


    解決:
    將網路封包設定小於 16383 bytes 即可


    影響範圍:
    據該 Issue 回報是 SQL Server 2008 SP1(10.0.2531.0)x64 有狀況
    但目前遇到的是 SQL Server 2008 R2(10.50.1600.1)x64 一樣也有狀況


    參考:
    http://support.microsoft.com/kb/2006769
    http://connect.microsoft.com/SQLServer/feedback/details/521172/the-savetosqlserver-method-has-encountered-ole-db-error-code-0x80004005#details
    http://blogs.msdn.com/b/dataaccesstechnologies/archive/2009/10/16/you-get-an-error-when-you-attempts-to-save-ssis-packages-to-the-msdb-package-store-if-the-network-packet-size-16388-bytes.aspx

     

    • 已標示為解答 Okawari 2011年3月21日 下午 06:10
    2011年3月21日 下午 06:04
  • Good to know, but why set packet size to that big number? Will lose more data when have network problem.  
    2011年3月22日 上午 01:47
  • 因為表單上 RichText 送出的文字量不定時會爆大量

    所以 submit 之後送到 SQL Server 的 SQL 會被截斷,導致 insert/update 失敗

    就直接先改網路封包大小比較快 XD

    2011年3月22日 上午 04:30