SQL Server 2012实施与管理实战指南(笔记)——Ch5启动SQL Server服务和数据库

在数据库和服务启动过程中，经常会出现的问题：

1.SQL Server实例无法正常启动

2.系统数据库无法正常启动

3.网络配置失败

4.用户数据库无法启动

5.集群环境下SQL Server资源无法保持在线

目录

5.启动SQL Server服务和数据库... 1

5.1 SQL Server服务启动步骤... 1

5.1.1 从注册表中读取SQL Server启动信息... 1

5.1.2 检查硬件，配置内存和CPU.. 2

5.1.3 数据库启动... 3

5.1.4 准备网络... 4

5.2 数据库状态切换... 5

5.2.1 Recovering. 5

5.2.2 Online. 6

5.2.3 RECOVERY PENDING.. 6

5.2.4 SUSPECT. 6

5.2.5 EMERGENCY. 6

5.2.6 RESTORING.. 6

5.2.7 Offline. 6

5.3 数据库长时间处于RECOVERING状态... 7

5.3.1 多线程并行处理重做和回滚... 7

5.3.2 延迟事务... 7

5.4 数据库无法启动的常见问题... 7

5.4.1 Master数据库不能启动... 7

5.4.2 资源数据库... 7

5.4.3 model数据库... 8

5.4.4 tempdb数据库... 8

5.4.5 用户数据库... 8

5.5 集群环境下，数据库资源不能ONLINE的常见问题... 10

Sqlservr.exe启动会去注册表中找自己所在实例的注册表信息。这些配置信息存放在HKEY_LOCAL_MACHINE/SOFTWARE/Microsoft/Microsoft SQL Server/MSSQL11.X/MSSQLServer下。有几个比较重要的信息：

1.HKEY_LOCAL_MACHINE/SOFTWARE/Microsoft/Microsoft SQL Server/MSSQL11.X/MSSQLServer下，LoginMode：表示是windows验证还是混合验证，Audit Level：设置是否记录用户登录信息。

2.HKEY_LOCAL_MACHINE/SOFTWARE/Microsoft/Microsoft SQL Server/MSSQL11.X/MSSQLServer /Parameters下是实例的启动参数

3.HKEY_LOCAL_MACHINE/SOFTWARE/Microsoft/Microsoft SQL Server/MSSQL11.X/MSSQLServer /SuperSocketNetLib下记录了监听的网络和访问方式的。

由于要读取注册表信息和SQL Server的资源文件，需要启动服务账号有很足够的权限。那么无法启动实例，甚至连errorlog都无法启动。

可以使用PRocess Monitor进行监控。如果发现有access DENIED那么说明有权限问题。

如果SQL Server要启动，必须正确的创建出日志文件，如果注册表内-e参数指定的不够正确就会出现报错，无法启动。

等创建出errorlog之后，所有的启动过程就可以从errorlog中看出来了。

Errorlog先会打印出SQL Server版本号和Windows版本号，启动参数和进程基本信息：

2014-05-04 14:42:07.05 Server Microsoft SQL Server 2014 - 12.0.2000.8 (X64)

Feb 20 2014 20:04:26

Copyright (c) Microsoft Corporation

Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)

2014-05-04 14:42:07.08 Server UTC adjustment: 8:00

2014-05-04 14:42:07.08 Server (c) Microsoft Corporation.

2014-05-04 14:42:07.08 Server All rights reserved.

2014-05-04 14:42:07.08 Server Server process ID is 4252.

2014-05-04 14:42:07.08 Server System Manufacturer: 'System manufacturer', System Model: 'System Product Name'.

2014-05-04 14:42:07.09 Server Authentication mode is MIXED.

2014-05-04 14:42:07.10 Server Logging SQL Server messages in file 'C:/Program Files/Microsoft SQL Server/MSSQL12.MSSQLSERVER/MSSQL/Log/ERRORLOG'.

2014-05-04 14:42:07.10 Server The service account is 'FanrCo-PC/Administrator'. This is an informational message; no user action is required.

2014-05-04 14:42:07.10 Server Registry startup parameters:

-d C:/Program Files/Microsoft SQL Server/MSSQL12.MSSQLSERVER/MSSQL/DATA/master.mdf

-e C:/Program Files/Microsoft SQL Server/MSSQL12.MSSQLSERVER/MSSQL/Log/ERRORLOG

-l C:/Program Files/Microsoft SQL Server/MSSQL12.MSSQLSERVER/MSSQL/DATA/mastlog.ldf

2014-05-04 14:42:07.10 Server Command Line Startup Parameters:

-s "MSSQLSERVER"

之后或读取出服务器的配置信息，包括处理器和内存，以及进程的优先级：

2014-05-04 14:42:08.73 Server SQL Server detected 1 sockets with 2 cores per socket and 2 logical processors per socket, 2 total logical processors; using 2 logical processors based on SQL Server licensing. This is an informational message; no user action is required.

2014-05-04 14:42:08.73 Server SQL Server is starting at normal priority base (=7). This is an informational message only. No user action is required.

2014-05-04 14:42:08.73 Server Detected 3838 MB of RAM. This is an informational message; no user action is required.

2014-05-04 14:42:08.76 Server Using conventional memory in the memory manager.

之后会显示NUMA信息和lock的信息，或为每个节点分配2500个锁块，5000个锁拥有者

注意，在32bit系统下，每个锁块需要64字节，锁拥有者32字节，64bit下，锁块96字节，锁拥有者56个字节：

2014-05-04 14:42:10.38 Server Node configuration: node 0: CPU mask: 0x0000000000000003:0 Active CPU mask: 0x0000000000000003: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-05-04 14:42:10.40 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.

显示CLR版本信息和默认TRACE的开启：

2014-05-04 14:42:11.33 Server CLR version v4.0.30319 loaded.

2014-05-04 14:42:12.44 spid8s Resource governor reconfiguration succeeded.

2014-05-04 14:42:12.49 spid8s SQL Server Audit is starting the audits. This is an informational message. No user action is required.

2014-05-04 14:42:12.60 spid8s SQL Server Audit has started the audits. This is an informational message. No user action is required.

2014-05-04 14:42:13.93 spid8s SQL Trace ID 1 was started by login "sa".

2014-05-04 14:42:14.09 spid8s Server name is 'FANRCO-PC'. This is an informational message only. No user action is required.

系统数据库的启动顺序是master，msdb，mssqlsystemresource，model，tempdb，也可以在errorlog中找到相关信息：

2014-05-04 14:42:10.57 spid8s Starting up database 'master'.

2014-05-04 14:42:16.67 spid21s Starting up database 'msdb'.

2014-05-04 14:42:16.67 spid22s Starting up database 'AdventureWorks2012'.

2014-05-04 14:42:16.74 spid10s Starting up database 'mssqlsystemresource'.

2014-05-04 14:42:16.74 spid23s Starting up database 'tst'.

2014-05-04 14:42:16.75 spid24s Starting up database 'AdventureWorksDW2012'.

2014-05-04 14:42:18.27 spid10s Starting up database 'model'.

2014-05-04 14:42:19.42 spid10s Clearing tempdb database.