1. 前言
在MSSQL服务器启动时，如果遇到服务器无法正常启动的情况，对于DBA和开发人员都是一个非常头疼的问题。本文将介绍如何有效地解决MSSQL服务器启动后即停止的情况。
2. 查看错误日志
当MSSQL服务器启动失败时，我们需要查看服务器错误日志。错误日志位于MSSQL安装目录下的LOG文件夹中，文件名类似于ERRORLOG或ERRORLOG.1。我们可以使用文本编辑器打开此文件，查看具体的错误信息。
2.1 示例代码
2020-10-13 10:59:23.89 Server Microsoft SQL Server 2019 (RTM-CU8) (KB4570012) - 15.0.4073.23 (X64)
Aug 28 2020 16:28:11
Copyright (C) 2019 Microsoft Corporation
Developer Edition (64-bit) on Windows 10 Pro 10.0 (Build 18363: ) (Hypervisor)
2020-10-13 10:59:24.91 Server UTC adjustment: 0:00
2020-10-13 10:59:24.91 Server (c) Microsoft Corporation.
2020-10-13 10:59:24.91 Server All rights reserved.
2020-10-13 10:59:24.91 Server Server process ID is 4536.
2020-10-13 10:59:24.91 Server System Manufacturer: 'VMware, Inc.', System Model: 'VMware Virtual Platform'.
2020-10-13 10:59:24.91 Server Authentication mode is MIXED.
2020-10-13 10:59:24.91 Server Logging SQL Server messages in file 'D:\MSSQL15.MSSQLSERVER\MSSQL\Log\ERRORLOG'.
2020-10-13 10:59:24.91 Server The service account is 'NT Service\MSSQLSERVER'. This is an informational message; no user action is required.
2020-10-13 10:59:24.91 Server Registry startup parameters:
-d D:\MSSQL15.MSSQLSERVER\MSSQL\DATA\master.mdf
-e D:\MSSQL15.MSSQLSERVER\MSSQL\Log\ERRORLOG
-l D:\MSSQL15.MSSQLSERVER\MSSQL\DATA\mastlog.ldf
2020-10-13 10:59:24.91 Server Command Line Startup Parameters:
-s "MSSQLSERVER"
2020-10-13 10:59:24.93 Server SQL Server detected 2 sockets with 6 cores per socket and 12 logical processors per socket, 24 total logical processors; using 24 logical processors based on SQL Server licensing. This is an informational message; no user action is required.
2020-10-13 10:59:24.93 Server SQL Server is starting at normal priority base (=7). This is an informational message only. No user action is required.
2020-10-13 10:59:24.93 Server Detected 8192 MB of RAM. This is an informational message; no user action is required.
2020-10-13 10:59:24.93 Server Using conventional memory in the memory manager.
2020-10-13 10:59:25.02 Server Default collation: Latin1_General_CI_AS (us_english 1033)
2020-10-13 10:59:25.02 Server Initializing the IDENTITY_INSERT values for system tables.
2020-10-13 10:59:25.04 Server Set AWE Enabled to 1 in the configuration parameters to allow use of more memory.
2020-10-13 10:59:25.04 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.
2020-10-13 10:59:25.05 Server Database Instant File Initialization: enabled. For security and performance considerations see the topic 'Database Instant File Initialization' in SQL Server Books Online. This is an informational message only. No user action is required.
2020-10-13 10:59:25.09 Server Total Log Writer threads: 2. This is an informational message; no user action is required.
2020-10-13 10:59:25.10 Server clflushopt is selected for pmem flush operation.
2020-10-13 10:59:25.10 spid7s Starting up database 'master'.
2020-10-13 10:59:25.14 spid7s 1 transactions rolled back in database 'master' (1:0). This is an informational message only. No user action is required.
2020-10-13 10:59:25.14 spid7s Recovery is complete. This is an informational message only. No user action is required.
2020-10-13 10:59:25.16 spid7s SQL Server Audit is starting the audits. This is an informational message. No user action is required.
2020-10-13 10:59:25.16 spid7s SQL Server Audit has started the audits. This is an informational message. No user action is required.
2020-10-13 10:59:25.19 Server CLR version v4.0.30319 loaded.
2020-10-13 10:59:25.22 spid7s Server name is ''. This is an informational message only. No user action is required.
2020-10-13 10:59:25.23 spid7s Always On: The availability replica manager is starting. This is an informational message only. No user action is required.
2020-10-13 10:59:25.23 spid7s Parallel redo is started for database 'master' with worker pool size [].
2020-10-13 10:59:25.24 spid7s Parallel redo is shutdown for database 'master' with worker pool size [].
2020-10-13 10:59:25.24 spid7s Recovery completed for database master (database ID 1) in 0 second(s) (analysis 149 ms, redo 0 ms, undo 0 ms). This is an informational message only. No user action is required.
2020-10-13 10:59:25.24 spid7s Parallel redo is started for database 'tempdb' with worker pool size [].
在实际应用中，错误日志中可能会输出大量信息，需要我们认真分析，找出出错的原因。
3. 快速定位问题
如果错误日志内容令人头疼，我们可以使用MSSQL的快速定位问题工具帮助我们定位问题。该工具可以自动帮助我们分析错误日志，并提供一些可能的解决方案。
4. 解决问题
根据错误日志或快速定位问题工具提供的指引，我们可以调整MSSQL服务器的配置，以解决启动失败的问题。
4.1 更改MSSQL服务器端口号
当MSSQL服务器启动端口被占用时，会导致MSSQL服务器启动失败。可以尝试更改MSSQL服务器的端口号来解决该问题。
--查看MSSQL服务器当前使用的端口号
SELECT DISTINCT local_tcp_port FROM sys.dm_exec_connections
WHERE protocol_type = 2 AND local_tcp_port IS NOT NULL;
--更改MSSQL服务器的端口号
USE master;
GO
EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE',
N'Software\Microsoft\MSSQLServer\MSSQLServer\SuperSocketNetLib\Tcp\IPAll',
N'TcpPort', REG_DWORD, 1433, 0;
GO
4.2 修复MSSQL数据库
MSSQL服务器启动时，会检查其管理的数据库是否完好，如果发现有损坏的数据库，会拒绝启动。可以使用以下命令修复MSSQL数据库。
--修复MSSQL数据库
USE database-name;
GO
EXEC sp_resetstatus 'database-name';
ALTER DATABASE database-name SET EMERGENCY;
DBCC checkdb('database-name');
ALTER DATABASE database-name SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
DBCC CheckDB (database-name, REPAIR_ALLOW_DATA_LOSS);
ALTER DATABASE database-name SET MULTI_USER;
GO
4.3 更新MSSQL服务器内存设置
当MSSQL服务器预留的内存不足时，会导致MSSQL服务器启动失败。可以通过以下命令更新MSSQL服务器的内存设置。
--更新MSSQL服务器内存设置
sp_configure 'max server memory', 2048;
GO
RECONFIGURE;
GO
5. 结论
对于MSSQL服务器启动后即停止的问题，我们可以通过查看错误日志、使用MSSQL的快速定位问题工具，以及调整MSSQL服务器的配置，来解决该问题。如果以上方法依然无法解决问题，可以考虑向MSSQL官方支持或社区寻求帮助。