对 Linux 上的 SQL Server 进行故障排除Troubleshoot SQL Server on Linux

适用对象:是SQL Server(仅限 Linux)否Azure SQL 数据库 否Azure SQL 数据仓库 否并行数据仓库 APPLIES TO: yesSQL Server (Linux only) noAzure SQL Database noAzure SQL Data Warehouse noParallel Data Warehouse

本文档介绍如何对 Linux 上或 Docker 容器中运行的 Microsoft SQL Server 进行故障排除。This document describes how to troubleshoot Microsoft SQL Server running on Linux or in a Docker container. 在对 Linux 上的 SQL Server 进行故障排除时,请记得查看 Linux 上的 SQL Server 发行说明中的支持功能和已知限制。When troubleshooting SQL Server on Linux, remember to review the supported features and known limitations in the SQL Server on Linux Release Notes.

提示

有关常见问题的解答,请参阅 Linux 上的 SQL Server 常见问题解答For answers to frequently asked questions, see the SQL Server on Linux FAQ.

解决连接失败问题Troubleshoot connection failures

如果在连接到 Linux SQL Server 时存在问题,可以检查以下几点。If you are having difficulty connecting to your Linux SQL Server, there are a few things to check.

  • 如果无法使用 localhost 进行本地连接,请尝试改用 IP 地址 127.0.0.1 。If you are unable to connect locally using localhost, try using the IP address 127.0.0.1 instead. Localhost 可能未正确映射到此地址 。It is possible that localhost is not properly mapped to this address.

  • 验证是否可从客户端计算机访问服务器名称或 IP 地址。Verify that the server name or IP address is reachable from your client machine.

    提示

    若要查找 Ubuntu 计算机的 IP 地址,可运行 ifconfig 命令,如以下示例所示:To find the IP address of your Ubuntu machine, you can run the ifconfig command as in the following example:

    sudo ifconfig eth0 | grep 'inet addr'
    

    对于 Red Hat,可使用 ip addr,如以下示例所示:For Red Hat, you can use the ip addr as in the following example:

    sudo ip addr show eth0 | grep "inet"
    

    但如果是 Azure VM,则此方法不适用。One exception to this technique relates to Azure VMs. 对于 Azure VM,请在 Azure 门户中查找 VM 的公共 IPFor Azure VMs, find the public IP for the VM in the Azure portal.

  • 如果适用,请检查是否已在防火墙上打开了 SQL Server 端口(默认为 1433)。If applicable, check that you have opened the SQL Server port (default 1433) on the firewall.

  • 对于 Azure VM,请检查是否有默认 SQL Server 端口的网络安全组规则For Azure VMs, check that you have a network security group rule for the default SQL Server port.

  • 验证用户名和密码是否存在任何拼写错误、多余空格或错误大小写。Verify that the user name and password do not contain any typos or extra spaces or incorrect casing.

  • 尝试以显式方式设置协议和端口号,确保服务器名称如下所示:tcp:servername,1433 。Try to explicitly set the protocol and port number with the server name like the following example: tcp:servername,1433.

  • 网络连接问题也可能导致连接错误和超时。Network connectivity issues can also cause connection errors and timeouts. 验证连接信息和网络连接后,请再次尝试连接。After verifying your connection information and network connectivity, try the connection again.

管理 SQL Server 服务Manage the SQL Server service

以下部分说明如何启动、停止、重启 SQL Server 服务并检查其状态。The following sections show how to start, stop, restart, and check the status of the SQL Server service.

在 Red Hat Enterprise Linux (RHEL) 和 Ubuntu 中管理 mssql-server 服务Manage the mssql-server service in Red Hat Enterprise Linux (RHEL) and Ubuntu

使用以下命令检查 SQL Server 服务的状态:Check the status of the SQL Server service using this command:

sudo systemctl status mssql-server

可根据需要使用以下命令停止、启动或重启 SQL Server 服务:You can stop, start, or restart the SQL Server service as needed using the following commands:

sudo systemctl stop mssql-server
sudo systemctl start mssql-server
sudo systemctl restart mssql-server

管理 mssql Docker 容器的执行Manage the execution of the mssql Docker container

通过运行以下命令,可以获得最新创建的 SQL Server Docker 容器的状态和容器 ID(ID 位于“CONTAINER ID”列下) :You can get the status and container ID of the latest created SQL Server Docker container by running the following command (The ID is under the CONTAINER ID column):

sudo docker ps -l

可根据需要使用以下命令停止或重启 SQL Server 服务:You can stop or restart the SQL Server service as needed using the following commands:

sudo docker stop <container ID>
sudo docker restart <container ID>

提示

有关 Docker 的更多故障排除提示,请参阅 SQL Server Docker 容器疑难解答For more troubleshooting tips for Docker, see Troubleshooting SQL Server Docker containers.

访问日志文件Access the log files

SQL Server 引擎在 Linux 和 Docker 安装的 /var/opt/mssql/log/errorlog 文件中进行记录。The SQL Server engine logs to the /var/opt/mssql/log/errorlog file in both the Linux and Docker installations. 需要启用“超级用户”模式才能浏览此目录。You need to be in 'superuser' mode to browse this directory.

安装程序在此处记录:/var/opt/mssql/setup-< time stamp representing time of install>。可使用任何 UTF-16 兼容工具(如“vim”或“cat”)浏览错误日志文件,如下所示:The installer logs here: /var/opt/mssql/setup-< time stamp representing time of install> You can browse the errorlog files with any UTF-16 compatible tool like 'vim' or 'cat' like this:

sudo cat errorlog

如果愿意,还可以使用以下命令将文件转换为 UTF-8,通过“more”或“less”读取它们:If you prefer, you can also convert the files to UTF-8 to read them with 'more' or 'less' with the following command:

sudo iconv -f UTF-16LE -t UTF-8 <errorlog> -o <output errorlog file>

扩展事件Extended events

可通过 SQL 命令查询扩展事件。Extended events can be queried via a SQL command. 可在此处找到扩展事件的详细信息:More information about extended events can be found here:

故障转储Crash dumps

在 Linux 中查看日志目录中的转储。Look for dumps in the log directory in Linux. 在 /var/opt/mssql/log 目录下查看 Linux Core 转储(扩展名为 .tar.gz2)或 SQL 小型转储(扩展名为 .mdmp)Check under the /var/opt/mssql/log directory for Linux Core dumps (.tar.gz2 extension) or SQL minidumps (.mdmp extension)

对于 Core 转储For Core dumps

sudo ls /var/opt/mssql/log | grep .tar.gz2 

对于 SQL 转储For SQL dumps

sudo ls /var/opt/mssql/log | grep .mdmp 

在最低配置或单用户模式下启动 SQL ServerStart SQL Server in Minimal Configuration or in Single User Mode

在最低配置模式下启动 SQL ServerStart SQL Server in Minimal Configuration Mode

在配置值的设置(例如,过度分配内存)妨碍服务器启动时,这非常有用。This is useful if the setting of a configuration value (for example, over-committing memory) has prevented the server from starting.

sudo -u mssql /opt/mssql/bin/sqlservr -f

在单用户模式下启动 SQL ServerStart SQL Server in Single User Mode

在某些情况下,可能必须使用 startup option -m 在单用户模式下启动 SQL Server 实例Under certain circumstances, you may have to start an instance of SQL Server in single-user mode by using the startup option -m. 例如,您可能要更改服务器配置选项或恢复已破坏的 master 数据库或其他系统数据库。For example, you may want to change server configuration options or recover a damaged master database or other system database. 例如,你可能希望更改服务器配置选项或恢复已破坏的 master 数据库或其他系统数据库For example, you may want to change server configuration options or recover a damaged master database or other system database

在单用户模式下启动 SQL ServerStart SQL Server in Single User Mode

sudo -u mssql /opt/mssql/bin/sqlservr -m

在单用户模式下使用 SQLCMD 启动 SQL ServerStart SQL Server in Single User Mode with SQLCMD

sudo -u mssql /opt/mssql/bin/sqlservr -m SQLCMD

警告

使用“mssql”用户启动 Linux 上的 SQL Server 以防止将来的启动问题。Start SQL Server on Linux with the "mssql" user to prevent future startup issues. 例如,“sudo -u mssql /opt/mssql/bin/sqlservr [STARTUP OPTIONS]”Example "sudo -u mssql /opt/mssql/bin/sqlservr [STARTUP OPTIONS]"

如果你不小心使用其他用户启动了 SQL Server,则必须先将 SQL Server 数据库文件的所有权更改回“mssql”用户,然后才能使用 systemd 启动 SQL Server。If you have accidentally started SQL Server with another user, you must change ownership of SQL Server database files back to the 'mssql' user prior to starting SQL Server with systemd. 例如,若要将 /var/opt/mssql 下所有数据库文件的所有权更改为“mssql”用户,请运行以下命令For example, to change ownership of all database files under /var/opt/mssql to the 'mssql' user, run the following command

chown -R mssql:mssql /var/opt/mssql/

重新生成系统数据库Rebuild system databases

作为最后手段,可以选择将 master 和模型数据库重新生成为默认版本。As a last resort, you can choose to rebuild the master and model databases back to default versions.

警告

这些步骤将删除已配置的所有 SQL Server 系统数据 !These steps will DELETE all SQL Server system data that you have configured! 这包括有关用户数据库的信息(但不包括用户数据库本身)。This includes information about your user databases (but not the user databases themselves). 它还将删除存储在系统数据库中的其他信息,包括以下各项:主密钥信息、在 master 中加载的任何证书、SA 登录密码、msdb 中的作业相关信息、msdb 中的 DB 邮件信息以及 sp_configure 选项。It will also delete other information stored in the system databases, including the following: master key information, any certs loaded in master, the SA Login password, job-related information from msdb, DB Mail information from msdb, and sp_configure options. 只有在了解其含义后才能使用!Only use if you understand the implications!

  1. 停止 SQL Server。Stop SQL Server.

    sudo systemctl stop mssql-server
    
  2. 使用“force-setup”参数运行 sqlservr 。Run sqlservr with the force-setup parameter.

    sudo -u mssql /opt/mssql/bin/sqlservr --force-setup
    

    警告

    请参阅上一个警告!See the previous warning! 此外,还必须以“mssql”用户身份运行,如下所示 。Also, you must run this as the mssql user as shown here.

  3. 看到消息“恢复已完成”后,请按 Ctrl+C。After you see the message "Recovery is complete", press CTRL+C. 这将关闭 SQL ServerThis will shut down SQL Server

  4. 重新配置 SA 密码。Reconfigure the SA password.

    sudo /opt/mssql/bin/mssql-conf set-sa-password
    
  5. 启动 SQL Server 并重新配置服务器。Start SQL Server and reconfigure the server. 这包括还原或重新附加任何用户数据库。This includes restoring or re-attaching any user databases.

    sudo systemctl start mssql-server
    

改善性能Improve performance

影响性能的因素有很多,包括数据库设计、硬件和工作负载需求。There are many factors that affect performance, including database design, hardware, and workload demands. 如果希望改善性能,请首先查看本文中的最佳做法,适用于 Linux 上的 SQL Server 的性能最佳做法和配置指南If you are looking to improve performance, start by reviewing the best practices in the article, Performance best practices and configuration guidelines for SQL Server on Linux. 然后,浏览一些可用于解决性能问题的工具。Then explore some of the available tools for troubleshooting performance problems.

常见问题Common issues

  1. 无法连接到远程 SQL Server 实例。You cannot connect to your remote SQL Server instance.

    请参阅连接到 Linux 上的 SQL Server 文章的疑难解答部分。See the troubleshooting section of the article, Connect to SQL Server on Linux.

  2. 错误:主机名不得超过 15 个字符。ERROR: Hostname must be 15 characters or less.

    这是一个已知问题,只要尝试安装 SQL Server Debian 包的计算机名超过 15 个字符就会出现此问题。This is a known-issue that happens whenever the name of the machine that is trying to install the SQL Server Debian package is longer than 15 characters. 除更改计算机名外,目前尚无其他解决方法。There are currently no workarounds other than changing the name of the machine. 可以编辑主机名文件并重启计算机以更改此名称。One way to achieve this is by editing the hostname file and rebooting the machine. 以下网站指南详细说明了此解决方法。The following website guide explains this in detail.

  3. 重置系统管理 (SA) 密码。Resetting the system administration (SA) password.

    如果忘记了系统管理员 (SA) 密码,或者出于其他原因需要重置密码,请遵循以下步骤。If you have forgotten the system administrator (SA) password or need to reset it for some other reason, follow these steps.

    备注

    以下步骤将暂时停止 SQL Server 服务。The following steps stop the SQL Server service temporarily.

    登录到主机终端,运行以下命令并按照提示重置 SA 密码:Log into the host terminal, run the following commands and follow the prompts to reset the SA password:

    sudo systemctl stop mssql-server
    sudo /opt/mssql/bin/mssql-conf setup
    
  4. 在密码中使用特殊字符。Using special characters in password.

    如果在 SQL Server 登录密码中使用某些字符,则可能需要使用反斜杠对其进行转义,然后再将其用于终端中的 Linux 命令。If you use some characters in the SQL Server login password, you might need to escape them with a backslash when you use them in a Linux command in the terminal. 例如,如果在终端命令/shell 脚本中使用美元符号 ($),则必须对其进行转义:For example, you must escape the dollar sign ($) anytime you use it in a terminal command/shell script:

    无效:Does not work:

    sudo sqlcmd -S myserver -U sa -P Test$$
    

    有效:Works:

    sqlcmd -S myserver -U sa -P Test\$\$
    

    资源:特殊字符 转义Resources: Special characters Escaping

info_tip 获取帮助Get help