SQL Server 日志和代理的错误日志

本文介绍的日志不是事务日志,而是SQL Server 日志和代理的错误日志,按照主体把错误日志分为SQL Server、SQL Server Agent、Database Mail,以及 Windows NT。SQL Server使用日志记录数据库引擎启动和运行过程中产生的信息,这些信息不一定是“错误”的消息,按照消息的严重程度,分为三个级别,分别是:信息(Information),警告(Warning)和错误(Error),SQL Server日志会按照消息发生的时间顺序记录消息。SQL Server日志用于记录SQL Server 引擎产生的消息,SQL Server Agent错误日志用于记录Agent运行过程中产生的信息。

一,存储日志的文件

默认情况下,日志文件存放的路径:C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Log,系统维护 7 个SQL Server日志文件,文件名分别是: ErrorLog,ErrorLog.n(n=1、2、3、4、5、6),其中,ErrorLog 文件是当前活跃的文件,包含最新的信息,ErrorLog.6 文件记录最早产生的消息。SQL Server每次都把消息记录在最新的文件ErrorLog中。生产环境中,ErrorLog文件的大小(Size)可能会很大,因此,需要定期清理这个错误日志文件,更新的方式是循环更新(Cycle),所谓循环更新的流程,是指:删除 ErrorLog.6 文件,将ErrorLog.5重命名为ErrorLog.6,依次类推,直到将ErrorLog重命名为ErrorLog.1,最后新建一个ErrorLog文件,用于记录SQL Server 运行过程中产生的信息。

系统维护10个SQL Server Agent 错误日志文件,代理错误日志文件存放的路径是:C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Log,文件名分别是是:SQLAgent.out、SQLAgent.n(n=1、2、3、4、5、6、7、8、9),其中SQLAgent.out记录代理当前的消息,其更新方式和SQL Server日志文件相同。

用户可以通过修改启动参数来修改日志文件的默认存储路径:修改以 –e开头的启动参数,把该参数后面的路径修改为新的路径,就可以将错误日志存放到指定的路径下,更新只有在重启服务之后,才会生效。

SQL Server日志和代理的错误日志文件,都使用循环更新来控制文件的大小,启动日志循环更新的进程,有2种触发方式:重启 SQL Server 实例,手动执行循环更新。通常来说,错误日志的大小是不受限制的,单个日志文件太大,这会导致文件的打开和查看都十分耗时。在实际操作中,通常需要建立一个Job定时去执行该存储过程,这样可以把日志文件的大小控制在合理的范围之内。

二,使用日志查看器查看消息

用户可以使用日志查看器来查看日志的内存,也可以使用TSQL命令。用户打开Management查看SQL Server Logs,选中"Current-xxx"来查看当前的日志文件,双击使用日志查看器打开日志。

用户也可以右击SQL Server Logs,对日志文件进行配置和Recycle,在日志查看器中,也可以通过Filter或Search来过滤和搜索日志。

三,使用TSQL 脚本查看消息

TSQL提供多个系统存储过程,用于查看日志文件的内容,对日志文件进行Recycle。

1,查看Errorlog的元数据

使用 sys.xp_enumerrorlogs 查看 ErrorLog 文件的创建日期和大小,其创建日期就是第一条记录插入的日期。

exec sys.xp_enumerrorlogs

2,读取ErrorLog的信息

SQL Server提供了存储过程sys.xp_readerrorlogsys.sp_readerrorlog,用于查看错误日志。

sys.xp_readerrorlog 存储过程有7个参数,按照参数的顺序,它们依次是:

  1. @Archive,存档编号(0~99),其值是 sys.xp_enumerrorlogs返回的Archive#字段的值, 默认值是0,0 代表的是ErrorLog,1代表的是ErrorLog.1。
  2. @Logtype,日志类型,有效值是1和2,1代表SQL Server日志,2代表SQL Server Agent日志,默认值是1。
  3. @SearchText1,查询包含的字符串,大小是255,默认值是null,
  4. @SearchText2,查询包含的字符串,大小是255,默认值是null,参数3和参数4的逻辑关系是and(与关系),表示同时包含这两个文本。
  5. @StartTime,消息的开始时间
  6. @End'Time,消息的结束时间
  7. @Order,对结果排序,按LogDate排序(Desc、Asc)对输出结果排序

sys.sp_readerrorlog有四个参数,和sys.xp_readerrorlog的前四个参数相同,sys.sp_readerrorlog内部使用sys.xp_readerrorlog来实现。

查看登陆失败的错误日志,可以看到参数4和参数5是过滤Text字段。

exec sys.sp_readerrorlog 0,1,'login','failed'

3,ErrorLog的循环更新

日志使用(Rollover)方式更新日志文件,如果当前活跃的ErrorLog 的文件很大,那么加载和查看的过程十分缓慢,可以运行 sys.sp_cycle_errorlog 或 DBCC ErrorLog,手动强制ErrorLog 文件迭代,避免单个ErrorLog的文件过大。

切换ErrorLog,查看ErrorLog 文件的信息

dbcc errorlog

对代理的错误日志文件进行迭代:

exec sp_cycle_agent_errorlog

每次启动 SQL Server 时,ErrorLog会自动迭代。由于ErrorLog的循环更新(Rollover)会删除最早创建的ErrorLog.6 文件,如果需要保存ErrorLog,在执行 sys.sp_cycle_errorlog 或 DBCC ErrorLog 之前,需要将ErrorLog.6 文件复制到其他存储介质上。

 

参考文档:

SQL Server 错误日志过滤(ERRORLOG)

SQL Server 错误日志收缩(ERRORLOG)

SQL SERVER – Read Error Log Data using sp_readerrorlog – System Stored Procedure

Reading the SQL Server log files using TSQL

Searching through the SQL Server error logs

posted @ 2018-05-22 11:42  悦光阴  阅读(9839)  评论(2编辑  收藏  举报