如何获取SQL Server中离线数据库的离线时间及操作登录名

我太懂你现在的困扰了——用系统视图只能拿到离线库的基础信息，但关键的离线时间和操作人就是找不到，甚至试了sp_readerrorlog也没头绪。别慌，咱们一步步来解决这个问题：

为什么现有方法拿不到信息？ #

sys.databases和sys.master_files这些系统视图只存储数据库的当前状态和文件信息，不会记录状态变更的时间和操作人；而默认用sp_readerrorlog可能因为没精准过滤关键词，导致你没找到对应的离线操作记录。

解决方案：从错误日志提取关键信息 #

当数据库被手动设置为离线时，SQL Server的错误日志会自动记录这条操作，格式大概是：

Database 'YourDBName' was set offline by login 'Domain\UserName'.

咱们可以用更灵活的日志读取方式来提取这些记录，再和你的离线库列表关联，就能得到你想要的完整信息。

第一步：精准提取离线操作的日志记录 #

用sys.fn_readerrorlog（比sp_readerrorlog更适合自定义筛选）来查询相关日志：

-- 筛选所有"设置离线"的操作记录，按时间倒序排列
SELECT 
    logdate,
    text
FROM sys.fn_readerrorlog(0, 1, 'was set offline by login', NULL, NULL, NULL, N'DESC')
WHERE text LIKE '%was set offline by login%'

• 参数说明：0代表当前错误日志文件，1指定是SQL Server日志类型，DESC让最新的记录排在最前面。如果要查更早的日志，把0换成1（上一个日志）、2（更早的）即可。

第二步：整合离线库列表与日志记录，生成目标格式 #

把上面的日志结果和你原来的离线库查询关联起来，就能得到符合要求的输出：

-- CTE1：获取离线数据库的基础信息
WITH OfflineDBs AS (
    SELECT 
        SERVERPROPERTY('ServerName') AS servername,
        db.name AS db_name,
        'offline' AS status,
        mf.name AS file_name,
        mf.type_desc AS file_type,
        mf.physical_name AS file_path
    FROM sys.databases db 
    INNER JOIN sys.master_files mf ON db.database_id = mf.database_id 
    WHERE db.state = 6 -- 6对应数据库离线状态
),
-- CTE2：从错误日志提取离线操作的时间和登录名
OfflineLogs AS (
    SELECT 
        -- 从日志文本中提取数据库名
        SUBSTRING(text, CHARINDEX('''', text) + 1, CHARINDEX(''' was set', text) - CHARINDEX('''', text) - 1) AS db_name,
        -- 从日志文本中提取登录名
        SUBSTRING(text, CHARINDEX('login ''', text) + 7, LEN(text) - CHARINDEX('login ''', text) - 7) AS login_name,
        -- 转换为你需要的dd/mm/yy日期格式
        CONVERT(VARCHAR, logdate, 103) AS offline_date
    FROM sys.fn_readerrorlog(0, 1, 'was set offline by login', NULL, NULL, NULL, N'DESC')
    WHERE text LIKE '%was set offline by login%'
)
-- 关联两个数据集，输出最终结果
SELECT 
    o.servername,
    o.db_name,
    o.status,
    ol.offline_date,
    ol.login_name
FROM OfflineDBs o
LEFT JOIN OfflineLogs ol ON o.db_name = ol.db_name
ORDER BY o.db_name

最终输出示例 #

运行上面的脚本后，就能得到你想要的格式：

注意事项 #

• 如果数据库是因为故障自动离线（比如磁盘损坏），错误日志里不会有操作登录名的记录，这种情况需要结合Windows事件查看器排查系统级问题。
• 如果离线操作发生在更早的日志文件里，记得调整sys.fn_readerrorlog的第一个参数（比如1、2）来遍历历史日志。

内容的提问来源于stack exchange，提问作者Sonal Akhal