解決“本機電腦上的MSSQLSERVER服務啟動後又停止了。一些服務自動停止，如果它們沒有什麼可做的”

SQL SERVER 2005 + SP3版本，突然無法啟動服務。帖出系統日誌中的錯誤：

 

2010-09-12 18:12:39.12 Server      Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86)
 Nov 24 2008 13:01:59
 Copyright (c) 1988-2005 Microsoft Corporation
 Enterprise Edition on Windows NT 5.2 (Build 3790: Service Pack 2)

2010-09-12 18:12:39.12 Server      (c) 2005 Microsoft Corporation.
2010-09-12 18:12:39.12 Server      All rights reserved.
2010-09-12 18:12:39.12 Server      Server process ID is 5368.
2010-09-12 18:12:39.12 Server      Authentication mode is MIXED.
2010-09-12 18:12:39.12 Server      Logging SQL Server messages in file 'C:/Program Files/Microsoft SQL Server/MSSQL.1/MSSQL/LOG/ERRORLOG'.
2010-09-12 18:12:39.12 Server      This instance of SQL Server last reported using a process ID of 5012 at 2010-9-12 18:11:51 (local) 2010-9-12 10:11:51 (UTC). This is an informational message only; no user action is required.
2010-09-12 18:12:39.12 Server      Registry startup parameters:
2010-09-12 18:12:39.12 Server        -d C:/Program Files/Microsoft SQL Server/MSSQL.1/MSSQL/DATA/master.mdf
2010-09-12 18:12:39.12 Server        -e C:/Program Files/Microsoft SQL Server/MSSQL.1/MSSQL/LOG/ERRORLOG
2010-09-12 18:12:39.12 Server        -l C:/Program Files/Microsoft SQL Server/MSSQL.1/MSSQL/DATA/mastlog.ldf
2010-09-12 18:12:39.12 伺服器         SQL Server is starting at normal priority base (=7). This is an informational message only. No user action is required.
2010-09-12 18:12:39.12 伺服器         Detected 2 CPUs. This is an informational message; no user action is required.
2010-09-12 18:12:39.29 伺服器         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.
2010-09-12 18:12:39.30 伺服器         Attempting to initialize Microsoft Distributed Transaction Coordinator (MS DTC). This is an informational message only. No user action is required.
2010-09-12 18:12:40.31 伺服器         Attempting to recover in-doubt distributed transactions involving Microsoft Distributed Transaction Coordinator (MS DTC). This is an informational message only. No user action is required.
2010-09-12 18:12:40.31 伺服器         Database mirroring has been enabled on this instance of SQL Server.
2010-09-12 18:12:40.31 spid4s      Starting up database 'master'.
2010-09-12 18:12:40.45 spid4s      SQL Trace ID 1 was started by login "sa".
2010-09-12 18:12:40.47 spid4s      Starting up database 'mssqlsystemresource'.
2010-09-12 18:12:40.48 spid4s      The resource database build version is 9.00.4035. This is an informational message only. No user action is required.
2010-09-12 18:12:40.61 spid4s      Server name is 'TEST_SERVER'. This is an informational message only. No user action is required.
2010-09-12 18:12:40.61 spid9s      Starting up database 'model'.
2010-09-12 18:12:40.61 伺服器         錯誤: 17182，嚴重性: 16，狀態: 1。
2010-09-12 18:12:40.61 伺服器         TDSSNIClient initialization failed with error 0x2, status code 0xd.
2010-09-12 18:12:40.61 伺服器         錯誤: 17182，嚴重性: 16，狀態: 1。
2010-09-12 18:12:40.61 伺服器         TDSSNIClient initialization failed with error 0x2, status code 0x1.
2010-09-12 18:12:40.61 伺服器         錯誤: 17826，嚴重性: 18，狀態: 3。
2010-09-12 18:12:40.61 伺服器         Could not start the network library because of an internal error in the network library. To determine the cause, review the errors immediately preceding this one in the error log.
2010-09-12 18:12:40.61 伺服器         錯誤: 17120，嚴重性: 16，狀態: 1。
2010-09-12 18:12:40.61 伺服器         SQL Server could not spawn FRunCM thread. Check the SQL Server error log and the Windows event logs for information about possible related problems.

原因分析：

根據這裡的文章提示，主要是紅色部分錯誤碼提示比較有用，原因是 SQL SERVER 組態管理->SQL SERVER 2005網路設定->MSSQLSERVER 的協議下TCP/IP的啟用與配置。出現上述錯誤的時候，TCP/IP無法啟動。

當你開啟它的屬性的時候，發現在“協議”那一欄，只有“保持活動狀態”，其它三個都沒有了。而在“IP 位址”欄，缺少IPALL。這個問題的解決辦法是去註冊表為TCP/IP增加丟失的項。如：

 

解決辦法：

一、開始->運行->regedit，找到 HKEY_LOCAL_MACHINE/SOFTWARE/Microsoft/Microsoft SQL Server/MSSQL.1/MSSQLServer/SuperSocketNetLib/Tcp
1)添加“Enabled”，類型dword，值為1。

 

2)添加IPALL項，並添加中的幾項。

 

添加完後回到SQL SERVER 組態管理中，你會看到，值都出現了，接下來你要做的事情就很簡單了，配置好IP地址，啟動它。

 

後記：昨天從下午4點多到晚上9點多，在機房呆了5個小時，就為瞭解決這個問題。上午只是對SQL SERVER 2005做過資料庫分離，轉移路徑後附加的操作。一切都是為了TFS2008的需要。當這一切都順利完成的時候，沒想到重起伺服器後就出現這個問題。為什麼註冊表資訊會丟失，這真是個迷。在杋房嘈雜的噪音中，我一度想過要重裝，但由於這台伺服器是TFS2008伺服器，重裝意味著。。。就是一個不折不扣的惡夢。
我要感謝誰呢。。。從機房回到辦公室，我坐在自己的電腦面前，電腦裡正是德雲社複演直播的畫面，小嶽在上面正樂麼呵的。呆看了幾分鐘，腦子裡一片空白，想著明天要如何交待這個事情。我在真正放棄的前一秒，不知道被什麼觸動了，想到了對比註冊表，結果半個小時搞定了。之前一切的想要去配置TCP/IP，甚至從光碟片恢複TCP/IP的網路設定等等等等。。。一切都只是時間的問題，oh yeah~~