SQL Server在CentOS上的日志管理指南

一、SQL Server日志文件的位置

在CentOS系统上，SQL Server的错误日志（记录启动、运行错误及常规信息）默认存储路径为/var/opt/mssql/log/，其中主要文件为errorlog（可通过ls /var/opt/mssql/log/确认）。此外，事务日志（.ldf文件）随数据库文件一起存储在/var/opt/mssql/data/目录下，文件名为数据库名_log.ldf。

二、查看SQL Server日志的方法

1. 查看错误日志

• 命令行方式：使用cat或less命令直接查看errorlog文件，例如：
  cat /var/opt/mssql/log/errorlog
less /var/opt/mssql/log/errorlog（支持翻页查看）。
• 系统日志：通过journalctl命令查看SQL Server服务的系统级日志，例如：
  journalctl -u mssql-server（实时滚动显示最新日志）。

2. 查看日志空间使用情况

使用sqlcmd工具连接SQL Server实例，执行以下T-SQL命令获取数据库日志空间使用率：

USE [master]; -- 切换到master数据库
GO
DBCC SQLPERF(LOGSPACE); -- 返回各数据库日志的总大小、已用空间及使用百分比
GO

三、日志维护核心操作

1. 收缩日志文件

当日志文件过大时，可通过以下步骤收缩（以testdb数据库为例）：

• 临时切换恢复模式（仅用于收缩操作，完成后需改回）：

  USE [master];
  GO
  ALTER DATABASE [testdb] SET RECOVERY SIMPLE; -- 切换至简单恢复模式（释放未使用的日志空间）
  GO
  

• 执行日志收缩：

  USE [master];
  GO
  DBCC SHRINKFILE (testdb_log, 100); -- 将日志文件收缩至100MB（需替换为实际日志文件名，可通过`sp_helpdb testdb`查看）
  GO
  

• 恢复恢复模式（若需完整恢复模式）：

  USE [master];
  GO
  ALTER DATABASE [testdb] SET RECOVERY FULL; -- 切换回完整恢复模式（支持事务日志备份）
  GO
  

  注：频繁收缩日志可能导致性能下降，建议仅在日志异常增大时操作。

2. 备份与截断事务日志

完整恢复模式下，需定期备份事务日志以释放空间（简单恢复模式下日志会自动截断）：

USE [master];
GO
BACKUP LOG [testdb] TO DISK = '/backups/testdb_logbackup.bak' WITH FORMAT; -- 格式化备份文件并备份日志
GO
DBCC SHRINKFILE (testdb_log, 100); -- 备份后收缩日志文件
GO

建议通过crontab设置定时任务，定期执行备份脚本（如每天凌晨2点执行）。

四、慢查询日志管理

SQL Server本身无专门的“慢查询日志”，但可通过扩展事件或SQL Server Profiler捕获慢查询。在CentOS上，推荐使用sqlcmd执行以下命令开启慢查询跟踪：

USE [master];
GO
-- 创建慢查询跟踪表（存储慢查询信息）
CREATE TABLE SlowQueries (
    QueryText NVARCHAR(MAX),
    ExecutionTime INT,
    LoginName NVARCHAR(128),
    StartTime DATETIME
);
GO
-- 使用扩展事件捕获慢查询（阈值设为1秒）
CREATE EVENT SESSION [SlowQueryCapture] ON SERVER 
ADD EVENT sqlserver.rpc_completed(
    ACTION(sqlserver.sql_text,sqlserver.login_name)
    WHERE ([duration] > 1000000) -- 单位：微秒（1秒=1000000微秒）
),
ADD EVENT sqlserver.sql_batch_completed(
    ACTION(sqlserver.sql_text,sqlserver.login_name)
    WHERE ([duration] > 1000000)
)
ADD TARGET package0.event_file(SET filename = '/var/opt/mssql/log/SlowQueries.xel');
GO
-- 启动跟踪
ALTER EVENT SESSION [SlowQueryCapture] ON SERVER STATE = START;
GO

慢查询日志会存储在/var/opt/mssql/log/SlowQueries.xel中，可通过sqlcmd或SSMS读取分析。

五、第三方日志管理工具

1. rsyslog

用于收集系统日志（包括SQL Server错误日志），并转发至远程日志服务器（如ELK）：

• 编辑rsyslog配置文件/etc/rsyslog.conf，添加以下内容：
  local0.* @192.168.1.100:514（将本地日志转发至IP为192.168.1.100的服务器，端口514）。
• 重启rsyslog服务：
  systemctl restart rsyslog。

2. ELK Stack（Elasticsearch+Logstash+Kibana）

实现日志的集中存储、搜索、可视化：

• Logstash：配置输入插件读取SQL Server日志文件（如/var/opt/mssql/log/errorlog），解析后发送至Elasticsearch。
• Kibana：通过可视化 dashboard 展示日志趋势、错误统计等信息。

六、自动化与监控

1. 自动化日志备份

使用crontab设置定时任务，例如每天凌晨2点备份所有数据库日志：

# 编辑当前用户的crontab
crontab -e

添加以下内容：

0 2 * * * /opt/mssql-tools/bin/sqlcmd -S MSSQLSERVER -Q "EXEC sp_msforeachdb 'BACKUP LOG [?] TO DISK=''\\backups\?.logbackup.bak'' WITH FORMAT'" >> /var/log/sqlserver_log_backup.log 2>&1

（sp_msforeachdb为系统存储过程，用于遍历所有数据库执行备份命令）。

2. 日志监控与告警

通过journalctl或grep命令监控错误日志中的关键字（如“error”“failed”），并结合邮件工具（如mailx）发送告警：

# 监控最近10分钟的错误日志
journalctl -u mssql-server --since "10 minutes ago" | grep -i "error"
# 若发现错误，发送邮件（需配置mailx）
echo "SQL Server出现错误，请检查日志！" | mailx -s "SQL Server错误告警" admin@example.com

通过以上步骤，可实现SQL Server在CentOS上的日志查看、维护、分析及自动化管理，保障数据库稳定运行并及时排查问题。