编程那点事 编程那点事编程那点事

   

SQL Server数据库单用户模式被占用解决实战(5064错误修复)

2025-12-08 10 阅读

问题场景

在为客户恢复商城数据库时，执行ALTER DATABASE语句时遇到以下错误：

消息 5064，级别 16，状态 1，第 3 行
此时无法更改数据库 'Dev50' 的状态或选项。此数据库处于单用户模式，当前某个用户已与其连接。

消息 5069，级别 16，状态 1，第 3 行
ALTER DATABASE 语句失败。

典型场景：

  • 商城数据库恢复后需要设置为多用户模式
  • 数据库从测试环境迁移到生产环境
  • 数据库维护后无法正常访问
  • SSMS或其他工具占用了数据库连接

问题分析

什么是单用户模式？

SQL Server的数据库访问模式分为三种：

模式 英文 说明 适用场景
多用户模式 MULTI_USER 允许多个用户同时连接(默认) 生产环境
单用户模式 SINGLE_USER 只允许一个连接 数据库维护、恢复操作
受限用户模式 RESTRICTED_USER 只允许特定用户连接 紧急维护

为什么会处于单用户模式？

常见原因：

  1. 数据库恢复：RESTORE DATABASE操作后默认为单用户模式
  2. 手动设置：之前执行过ALTER DATABASE SET SINGLE_USER
  3. 维护操作：数据库维护任务未正确结束
  4. 异常中断：上次操作异常中断

谁占用了连接？

可能的占用者：

  • SQL Server Management Studio (SSMS)
  • 应用程序连接池
  • 其他管理工具(Navicat、DBeaver等)
  • 系统进程
  • 你自己的查询窗口

解决方案(5种方法)

方案1：强制断开连接(推荐)

最快最有效的方法，立即回滚所有事务并切换到多用户模式：

USE master;
GO

-- 强制断开所有连接并设置为多用户模式
ALTER DATABASE [Dev50] 
SET MULTI_USER WITH ROLLBACK IMMEDIATE;
GO

-- 验证数据库状态
SELECT 
    name AS 数据库名,
    user_access_desc AS 访问模式
FROM sys.databases 
WHERE name = 'Dev50';
GO

优点：

  • 一条命令搞定
  • 无需查找session_id
  • 自动处理未完成事务

注意事项：

  • 会回滚所有未完成的事务
  • 会强制断开所有连接
  • 建议在非业务高峰期执行

方案2：查找并杀掉占用连接

适合需要精确控制的场景，先查找占用的会话，再手动杀掉：

步骤1：查找占用的会话

USE master;
GO

SELECT 
    session_id AS 会话ID,
    login_name AS 登录用户,
    host_name AS 主机名,
    program_name AS 程序名,
    status AS 状态,
    login_time AS 登录时间,
    last_request_start_time AS 最后请求时间
FROM sys.dm_exec_sessions
WHERE database_id = DB_ID('Dev50');
GO

示例输出：

会话ID  登录用户      主机名          程序名                    状态
------  ----------    ------------    ----------------------    -------
52      sa            DESKTOP-ABC     Microsoft SQL Server...   sleeping

步骤2：杀掉占用的会话

-- 替换52为实际的session_id
KILL 52;
GO

步骤3：设置为多用户模式

ALTER DATABASE [Dev50] 
SET MULTI_USER;
GO

优点：

  • 可以看到是谁占用的
  • 精确控制
  • 可以先尝试联系占用者

方案3：使用ROLLBACK AFTER N SECONDS

适合给用户缓冲时间的场景，等待N秒后自动回滚：

USE master;
GO

-- 等待30秒，如果还有连接则强制回滚
ALTER DATABASE [Dev50] 
SET MULTI_USER WITH ROLLBACK AFTER 30 SECONDS;
GO

优点：

  • 给用户时间保存工作
  • 更人性化
  • 自动处理

适用场景：

  • 生产环境切换
  • 有用户在线的情况
  • 不想立即中断连接

方案4：关闭所有SSMS连接

适合开发环境，手动关闭可能占用的工具：

步骤：

  1. 关闭所有SQL Server Management Studio窗口
  2. 关闭Navicat、DBeaver等数据库工具
  3. 重启相关应用程序
  4. 执行方案1的命令

优点：

  • 不强制杀进程
  • 更温和
  • 适合本地开发

方案5：重启SQL Server服务（最后手段）

极端情况下使用，重启SQL Server服务：

命令行方式：

# 以管理员身份运行PowerShell

# 重启SQL Server服务（默认实例）
Restart-Service -Name MSSQLSERVER

# 或者重启命名实例
Restart-Service -Name "MSSQL$实例名"

图形界面方式：

服务管理器 (services.msc)
→ 找到 SQL Server (MSSQLSERVER)
→ 右键 → 重新启动

重启后执行：

ALTER DATABASE [Dev50] 
SET MULTI_USER;
GO

缺点：

  • 影响所有数据库
  • 中断所有连接
  • 需要服务器权限
  • 仅作为最后手段

商城数据恢复完整流程

结合实际商城数据库恢复场景，完整流程如下：

步骤1：准备工作

-- 1. 检查磁盘空间
EXEC sp_spaceused;
GO

-- 2. 查看当前数据库状态
SELECT 
    name,
    state_desc AS 状态,
    user_access_desc AS 访问模式,
    recovery_model_desc AS 恢复模式
FROM sys.databases
WHERE name = 'Dev50';
GO

步骤2：恢复数据库

USE master;
GO

-- 设置为单用户模式（准备恢复）
ALTER DATABASE [Dev50] 
SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
GO

-- 恢复数据库（示例）
RESTORE DATABASE [Dev50]
FROM DISK = 'D:\Backup\Dev50_20251030.bak'
WITH REPLACE,
MOVE 'Dev50' TO 'D:\Data\Dev50.mdf',
MOVE 'Dev50_log' TO 'D:\Data\Dev50_log.ldf';
GO

步骤3：设置为多用户模式（重点）

-- 强制设置为多用户模式
ALTER DATABASE [Dev50] 
SET MULTI_USER WITH ROLLBACK IMMEDIATE;
GO

步骤4：验证数据库

-- 1. 检查数据库状态
SELECT 
    name,
    state_desc,
    user_access_desc
FROM sys.databases
WHERE name = 'Dev50';
GO

-- 2. 测试连接
USE [Dev50];
GO

-- 3. 检查表数量
SELECT COUNT(*) AS 表数量
FROM sys.tables;
GO

-- 4. 检查关键表数据（商城示例）
SELECT COUNT(*) AS 商品数量 FROM Products;
SELECT COUNT(*) AS 订单数量 FROM Orders;
SELECT COUNT(*) AS 用户数量 FROM Users;
GO

步骤5：设置权限

-- 1. 创建应用程序登录账号
CREATE LOGIN mall_app 
WITH PASSWORD = 'YourStrongPassword123!';
GO

-- 2. 创建数据库用户
USE [Dev50];
GO

CREATE USER mall_app FOR LOGIN mall_app;
GO

-- 3. 授权
ALTER ROLE db_owner ADD MEMBER mall_app;
GO

-- 或者精确授权（推荐）
GRANT SELECT, INSERT, UPDATE, DELETE ON SCHEMA::dbo TO mall_app;
GO

步骤6：修改连接字符串

// appsettings.json
{
  "ConnectionStrings": {
    "DefaultConnection": "Server=localhost;Database=Dev50;User Id=mall_app;Password=YourStrongPassword123!;TrustServerCertificate=True;"
  }
}

预防措施

1. 恢复操作使用脚本

创建标准化的数据库恢复脚本：

-- restore_database.sql
USE master;
GO

DECLARE @DatabaseName NVARCHAR(128) = 'Dev50';
DECLARE @BackupFile NVARCHAR(512) = 'D:\Backup\Dev50_20251030.bak';

-- 强制断开所有连接
PRINT '1. 断开所有连接...';
EXEC('ALTER DATABASE [' + @DatabaseName + '] SET SINGLE_USER WITH ROLLBACK IMMEDIATE');

-- 恢复数据库
PRINT '2. 恢复数据库...';
RESTORE DATABASE @DatabaseName
FROM DISK = @BackupFile
WITH REPLACE;

-- 设置为多用户模式
PRINT '3. 设置为多用户模式...';
EXEC('ALTER DATABASE [' + @DatabaseName + '] SET MULTI_USER');

-- 验证
PRINT '4. 验证数据库状态...';
SELECT name, user_access_desc FROM sys.databases WHERE name = @DatabaseName;

PRINT '恢复完成！';
GO

2. 监控数据库连接

创建监控脚本，定时检查数据库状态：

-- monitor_database.sql
SELECT 
    d.name AS 数据库名,
    d.user_access_desc AS 访问模式,
    COUNT(s.session_id) AS 连接数
FROM sys.databases d
LEFT JOIN sys.dm_exec_sessions s ON d.database_id = s.database_id
WHERE d.name = 'Dev50'
GROUP BY d.name, d.user_access_desc;
GO

3. 使用连接池管理

C# 示例：

// 正确的连接字符串配置
var connectionString = 
    "Server=localhost;" +
    "Database=Dev50;" +
    "User Id=mall_app;" +
    "Password=YourPassword;" +
    "Min Pool Size=5;" +         // 最小连接池
    "Max Pool Size=100;" +       // 最大连接池
    "Connection Timeout=30;" +   // 连接超时
    "TrustServerCertificate=True;";

// 确保连接及时释放
using (var connection = new SqlConnection(connectionString))
{
    connection.Open();
    // 执行操作
} // 自动释放连接

4. 定期清理连接

-- 每天定时执行，清理超过1小时的睡眠连接
DECLARE @SessionId INT;
DECLARE session_cursor CURSOR FOR
SELECT session_id
FROM sys.dm_exec_sessions
WHERE database_id = DB_ID('Dev50')
  AND status = 'sleeping'
  AND DATEDIFF(HOUR, last_request_end_time, GETDATE()) > 1;

OPEN session_cursor;
FETCH NEXT FROM session_cursor INTO @SessionId;

WHILE @@FETCH_STATUS = 0
BEGIN
    PRINT '清理会话: ' + CAST(@SessionId AS VARCHAR);
    EXEC('KILL ' + @SessionId);
    FETCH NEXT FROM session_cursor INTO @SessionId;
END

CLOSE session_cursor;
DEALLOCATE session_cursor;
GO

常见错误对照表

错误代码 错误信息 原因 解决方案
5064 数据库处于单用户模式 有连接占用 使用ROLLBACK IMMEDIATE
5069 ALTER DATABASE失败 无法修改数据库状态 先杀掉占用连接
3101 数据库正在使用 有活动连接 强制断开连接
5030 无法排他锁定数据库 数据库被占用 关闭所有工具
924 数据库已附加 数据库状态异常 重启SQL Server

故障排查清单

遇到问题时，按以下顺序排查：

□ 1. 确认数据库名称正确
□ 2. 检查当前用户是否有权限
□ 3. 查看数据库是否处于单用户模式
□ 4. 查找占用的session_id
□ 5. 检查是否是自己的SSMS窗口占用
□ 6. 尝试强制断开连接
□ 7. 验证多用户模式设置成功
□ 8. 测试应用程序连接

最佳实践建议

推荐做法

  1. 使用脚本化恢复流程

    • 避免手动操作遗漏
    • 标准化恢复步骤
    • 可追溯可重复

  2. 恢复前备份当前数据库

    BACKUP DATABASE [Dev50] 
TO DISK = 'D:\Backup\Dev50_BeforeRestore.bak';

  3. 在维护窗口执行

    • 避免业务高峰期
    • 提前通知相关人员
    • 准备回滚方案

  4. 使用专用维护账号

    • 不使用sa账号
    • 权限最小化原则
    • 操作可审计

避免做法

  1. 在生产环境直接执行KILL命令
  2. 不检查就重启SQL Server服务
  3. 恢复后不验证数据完整性
  4. 使用弱密码或默认密码
  5. 不记录操作日志

总结

问题核心：数据库处于单用户模式且被连接占用

最快解决方案：

ALTER DATABASE [Dev50] SET MULTI_USER WITH ROLLBACK IMMEDIATE;

关键要点：

  1. 使用WITH ROLLBACK IMMEDIATE强制断开连接
  2. 恢复操作使用脚本自动化
  3. 恢复后验证数据完整性
  4. 设置合理的连接池参数
  5. 定期监控数据库连接状态

适用场景：

  • 商城数据库恢复
  • 数据库迁移部署
  • 测试环境搭建
  • 数据库维护操作

遇到问题？

如果按照以上方法仍无法解决，可能遇到以下特殊情况：

  1. 数据库处于恢复状态：等待恢复完成或检查日志
  2. 文件被锁定：检查防病毒软件或备份工具
  3. 权限不足：使用sysadmin角色账号操作
  4. 系统表损坏：考虑从备份恢复

欢迎在评论区分享你的问题和解决方案！

标签： 数据库运维 错误修复 数据库恢复 SQL Server维护

置顶推荐

热门文章

随机推荐

热门专题

热门标签

系统 企业 项目 客户 管理 数据 功能 平台 程序 开发 设置 税务 金税四期乐企平台 乐企对接 乐企发票接口 乐企接口申请 乐企接入 乐企平台接口 乐企平台接口文档 乐企全电发票

网站统计

  • 日志总数：1299
  • 分类总数：47
  • 标签总数：1287
  • 专题总数：0
  • 最后更新：2025-12-08

编程那点事 更专业 更方便

登录

找回密码

注册