SQL SERVER 服务启动后停止，某些服务由其它服务或程序使用时将自动停止

shirobert

　　发生症状： 先是服务器挂掉，之后服务器可以登陆，但是实例却不能登陆进去（部分）。出现的错误日志如下：



2014-01-24 09:37:00.14 Server      Microsoft SQL Server 2008 R2 (SP2) - 10.50.4000.0 (X64)
Jun 28 2012 08:36:30
Copyright (c) Microsoft Corporation
Enterprise Edition (64-bit) on Windows NT 6.1  (Build 7600: )
2014-01-24 09:37:00.15 Server      (c) Microsoft Corporation.
2014-01-24 09:37:00.15 Server      All rights reserved.
2014-01-24 09:37:00.15 Server      Server process ID is 5732.
2014-01-24 09:37:00.15 Server      System Manufacturer: 'Dell Inc.', System Model: 'PowerEdge R410'.
2014-01-24 09:37:00.15 Server      Authentication mode is MIXED.
2014-01-24 09:37:00.15 Server      Logging SQL Server messages in file 'D:\SQL_2K8\MSSQL10_50.XXXXXXX\MSSQL\Log\ERRORLOG'.
2014-01-24 09:37:00.15 Server      This instance of SQL Server last reported using a process ID of 5360 at 2014/1/24 9:36:44 (local) 2014/1/24 1:36:44 (UTC). This is an informational message only; no user action is required.
2014-01-24 09:37:00.15 Server      Registry startup parameters:
-d D:\SQL_2K8\MSSQL10_50.XXXXXXX\MSSQL\DATA\master.mdf
-e D:\SQL_2K8\MSSQL10_50.XXXXXXX\MSSQL\Log\ERRORLOG
-l D:\SQL_2K8\MSSQL10_50.XXXXXXX\MSSQL\DATA\mastlog.ldf
2014-01-24 09:37:00.16 Server      SQL Server is starting at normal priority base (=7). This is an informational message only. No user action is required.
2014-01-24 09:37:00.16 Server      Detected 8 CPUs. This is an informational message; no user action is required.
2014-01-24 09:37:00.16 Server      Large Page Extensions enabled.
2014-01-24 09:37:00.16 Server      Large Page Granularity: 2097152
2014-01-24 09:37:00.17 Server      Large Page Allocated: 32MB
2014-01-24 09:37:00.17 Server      Large Page Allocated: 32MB
2014-01-24 09:37:00.18 Server      Using locked pages for buffer pool.
2014-01-24 09:37:00.23 Server      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.
2014-01-24 09:37:00.25 Server      Node configuration: node 0: CPU mask: 0x00000000000000f0:0 Active CPU mask: 0x00000000000000f0:0. This message provides a description of the NUMA configuration for this computer. This is an informational message only. No user action is required.
2014-01-24 09:37:00.25 Server      Node configuration: node 1: CPU mask: 0x000000000000000f:0 Active CPU mask: 0x000000000000000f:0. This message provides a description of the NUMA configuration for this computer. This is an informational message only. No user action is required.
2014-01-24 09:37:00.28 spid6s      Starting up database 'master'.
2014-01-24 09:37:00.33 spid6s      Resource governor reconfiguration succeeded.
2014-01-24 09:37:00.33 spid6s      SQL Server Audit is starting the audits. This is an informational message. No user action is required.
2014-01-24 09:37:00.33 spid6s      SQL Server Audit has started the audits. This is an informational message. No user action is required.
2014-01-24 09:37:00.33 spid6s      FILESTREAM: effective level = 0, configured level = 0, file system access share name = 'XXXXXXX'.
2014-01-24 09:37:00.34 spid6s      SQL Trace ID 1 was started by login "sa".
2014-01-24 09:37:00.34 spid6s      Starting up database 'mssqlsystemresource'.
2014-01-24 09:37:00.35 spid6s      Error: 17204, Severity: 16, State: 1.
2014-01-24 09:37:00.35 spid6s      FCB::Open failed: Could not open file C:\Program Files\Microsoft SQL Server\MSSQL10_50.XXXXXXX\MSSQL\Binn\mssqlsystemresource.mdf for file number 1.  OS error: 2(系统找不到指定的文件。).
2014-01-24 09:37:00.35 spid6s      Error: 5120, Severity: 16, State: 101.
2014-01-24 09:37:00.35 spid6s      Unable to open the physical file "C:\Program Files\Microsoft SQL Server\MSSQL10_50.XXXXXXX\MSSQL\Binn\mssqlsystemresource.mdf". Operating system error 2: "2(系统找不到指定的文件。)".
2014-01-24 09:37:00.35 spid6s      Error: 17207, Severity: 16, State: 1.
2014-01-24 09:37:00.35 spid6s      FileMgr::StartLogFiles: Operating system error 2(系统找不到指定的文件。) occurred while creating or opening file 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.XXXXXXX\MSSQL\Binn\mssqlsystemresource.ldf'. Diagnose and correct the operating system error, and retry the operation.
2014-01-24 09:37:00.35 spid6s      File activation failure. The physical file name "C:\Program Files\Microsoft SQL Server\MSSQL10_50.XXXXXXX\MSSQL\Binn\mssqlsystemresource.ldf" may be incorrect.
2014-01-24 09:37:00.35 spid6s      Error: 945, Severity: 14, State: 2.
2014-01-24 09:37:00.35 spid6s      Database 'mssqlsystemresource' cannot be opened due to inaccessible files or insufficient memory or disk space.  See the SQL Server errorlog for details.
2014-01-24 09:37:00.35 spid6s      SQL Trace was stopped due to server shutdown. Trace ID = '1'. This is an informational message only; no user action is required.
　　从上面可以看到是系统启动的时候找不到文件，而有一部分的系统文件是在迁移在D盘了的。
　　解决办法： 因为mssqlsystemresource这个数据库存放的是系统存储过程之类的东东，相同的版本都是相同的，所以可以拿版本一样的替换就可以了。
原因： 估计是装 service package， 或者系统补丁， 有些东西要重启才生效，而且控制得有问题，所以重启后要让某些东西生效，但是这个东东被删除了没有恢复
其它知识补充：   mssqlsystemresource database is removed after SQL Server 2008 R2 SP1 installation
　　SQL Server 2005的Resource数据库