发生症状: 先是服务器挂掉,之后服务器可以登陆,但是实例却不能登陆进去(部分)。出现的错误日志如下:
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 <X64> (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 = ''. 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